Limit or group duplicates in table based on one field?

App Building
1

image
image1482×548 25.8 KB

My spreadsheet looks like this. Can exclude duplicates from it based on login?(Counter is preferable too, to show how many of them=) but even hiding would be more then enough.
Could you advise please?

2

Hello!

You could use the Query JSON with SQL resource to filter down the table data with a DISTINCT clause:

Select distinct login, yourOtherFields from {{yourTableQuery.data}}

Or filter the table data array with a lodash uniq function:

_.uniqBy(yourTableQuery.data, 'login')

1 Like
3

Wow, didn't know something like that is possible. Lodash solves the problem, but how to use your second option with Query JSON with SQL? Where to put it in retool?

4

One of the preset resources is called Query JSON with SQL:

image

You can use this resource in apps and workflows!

5

Could you advise on what i do wrong?

image
image1020×976 42.7 KB

And the getdata request

image
image982×1126 59.9 KB

6

Since the getData query is a SQL query, you might be able to just use ... from {{getData}}

This seems to be how they describe selecting from a SQL query in the Retool Documentation for Query JSON with SQL (Query JSON with SQL | Retool Docs)

7

Did a little testing.

It seems like you actually want to make the query data an array for it to be properly selectable:

select distinct login, id from {{formatDataAsArray(getData.data)}}

8

Thank you pyrrho! What a struggle it was, to make it work) There is always a space needed after formatDataAsArray - otherwise it didn't work. Now it looks like this, BUT) Logins are still not unique)

image
image1836×1180 224 KB

9

Weird about the space... I don't think I've needed to dot that before, but hey! It works. If you just want distinct logins, then only query the login value. When you include the id, it is returning distinct pairs of login, id. If you query select distinct login from {{formatDataAsArray(getData.data)}} you will get the list of only the unique login values.

I'm pretty sure you could rejoin the data on itself to get the count of Ids for each unique login, but my SQL isn't that good for me to remember off the top of my head the right syntax for that.

1 Like
10

Thank you so much, Pyrrho, for all your help. Now I understand.
Your first solution with lodash turned out to be much easier and works without joins, so I'll stick with it. However, I was very curious about the other SQL-like option.
Have a great day!

//gosh i love this community!

2 Likes
11

Love to see all the knowledge sharing and partnership on working toward solutions, and love to see the love for the community expressed too!! Great work and thank you for sharing @Bogdan_Mind @pyrrho :tada: :sparkles:

2 Likes