Create a table with one row for each unique value in a column from another table

Ive read the other topics and thought id made progress, but ive become stuck.

I have a bunch of jobs in one table, with company name in one column. I want to make a second table with a list of the unique company names in it. I was going to use the row number method and then query the column, and grab the Nth value from unique values from the array, but it seems like when using the query column, I cant choose the index in the array i want, and with the lookup table, it limits the array to 100 values (there are 9000 values in this array i need a separate row for).

Can someone offer any solution?

Can you show screenshots of what you tried? I’ve never heard of the Lookup column being limited. I would think the Row numbering, followed by a Lookup column of Companies, a Unique Elements column to get a unique list from the lookup, and finally a Single Value column to pull individual items from the unique array should work.

I suspect it’s either a Big Table or an SQL data source. Some operations on large data sources are limited to 100 rows.

Assuming that it is, this represents a bit of a challenge. I can’t think of a solution off the top of my head.

1 Like

@Northbridge_Recruitm - can you tell me roughly how many unique company names you have in the table? If it’s less than 100, and you are working with a Big Table, then I might have a solution for you.

Nope, scratch that. the solution I had in mind doesn’t work. I think on it a bit, and if I manage to find a solution I’ll come back.

Have you tried to use single value by index instead? Nor you have to have this 9000 rows already in the table, but it can work if I understood correctly.

As you have single row with array you need, you import it in your big table as first single value:

And then separating this single value for each row by index using single value:

It is a big table.

The table has 50,000 rows, which is why i chose a big table. Is there anything preventing me from creating the same in a standard Glide table then and doing it that way? Wasnt sure what the row limits were between a standard glide table and a glide big table.

The issue is that of the 50,000 jobs, there are about 9000 companies featured, but these companies are included in a total list of about 60,000 companies. I was trying to avoid having to put all of them in a table, and instead limit it to only the 9000 that I needed.

I think your best option is to move the data to an SQL data source. Then it’s just a simple query.

1 Like