Distinct values from array column

Ask for Help
1

My table looks something like this:

            playerID                   arrOpponentIDs

row 1 playerID=1 arrOpponentIDs = {5,2,7}
row 2 playerID=2 arrOpponentIDs = {9,4,3,1}
row 3 playerID=1 arrOpponentIDs = {2,5,8}
…
row N

I need to calculate an ‘aggregate’ view of unique opponentIDs from the array column, so the resulting column in case of playerID=1 should be: 5,2,7,8. The column could be in this table or in the parent player table.

I did the relation to itself to this table by linking player ID and got the following for playerID=1:

{5,2,7},{2,5,8}

But now, don’t know how to extract aggregate distinct values. Tried playing with join list, but stuck.

Thanks in advance.

2

This might help!

3

Thanks for quick reply @Eitan , but I tried this already and it doesn’t help.

The problem is my arrOpponentIDs column contains values that are already arrays:
row1 = {5,2,7}
row3 = {2,5,8}
rowN …

Unique elements won’t remove anything in this case. If I aggregate all columns for certain playerID, I will get a column with arrays of arrays : {{5,2,7},{2,5,8}} and Unique column doesn’t help removing as there are no redundant elements (subarray elements are different though).

Just to remind, I need a resulting column having {5,2,7,8}

4

Is that really how it shows in your data with the curly brackets? If so, that is not an array in Glide’s eyes. If it was truly an array, each value would be in it’s own bubble. Where are those bracketed values coming from?

1 Like
5

well you need to get those two arrays, {5,2,7},{2,5,8}, into a single comma array: 5,2,7,2,5,8 and then you can create them into a glide array and then from there you can remove the unique values…

I’m also not sure where those curly braces are coming from, but my first thoughts are to initially remove the curly braces from each initial array, so row 1 to: 5,2,7 and row 3 to 2,5,8 – perhaps with Text Slice plugin. Then perform your relation to get: 5,2,7,2,5,8 then use Split text to convert to an array and then you can remove the unique values

if that makes sense? or maybe someone has a better idea, this is just an initial thought and might not be the most efficient

1 Like
6

Not curly brackets, sorry, it is pure array obtained from relation lookup.

7

There’s a few ways to approach this which @Eitan touched on. I guess before I chime in, I’d like a little more information to provide the most efficient solution.

What does the relation look like that leads to the lookup array in each row? An alternative is a Joined List column instead of a Lookup column, which would get you on track to @Eitan’s solution. But is it possible that something could be done directly in the player table instead?

What is the reason for making the values unique? How do you intend to use them? If it’s too build another relation, the values don’t need to be unique.

1 Like
8

I solved the issue with joined list, aggregated in a string and then used js to remove duplicates to get unique IDs, then converted to array so I can use it in relation.

As for your question - it is too complex to explain. Basically my app is for managing tennis league:

The club marks all times and courts when matches could be played

Then players mark times when then can play

Then the app try to match possible opponents.

9

If the whole goal is to use it for a relation, there is absolutely no reason to make the array unique. A relation isn’t going to make up duplicate rows out of thin air. It’s only going to find one match even if the array contains duplicate items. I wouldn’t even bother with attempting to get a unique array if the only goal is to use for a relation. It’s not going to change the results and just adds unnecessary extra computations.

You’re on track with the joined list, but I would have used a Split Text column to convert it into an array, followed by a Unique Elements column to only get an array of unique array items. No javascript necessary. But I really think you don’t need that unique part in your case.

2 Likes