How to join if Value found in Array

[4d78bb8719d997eb9a26]

Hi,

I would like to combine data sets based on a key found in an array of keys:

Example: A is in [A,B,C]

Practical application: I have data-set A as per-email data on one hand, and data-set B as per-person email array on the other hand. I would need to bring in data from A to B. That is, if email in A-row matches with an email in email array of B-row, then join data.

Data Set A

john@gmail.com ; USA

sandy@yahoo.com; UK

Data Set B

[john@gmail.com, john@hotmail.com] ; John Doe

[sandy@yahoo.com, sandy.b@company.com]; Sandy Smith

Joined A and B

[john@gmail.com, john@hotmail.com] ; John Doe; USA

[sandy@yahoo.com, sandy.b@company.com]; Sandy Smith; UK

[Trifacta_Alumni]

Hi @Benjamin Sasin?,

I have arrived at a rather inelegant solution. I'll investigate further. In the meantime...

Recipe B:

1. Convert the array of addresses into a list, e.g., [address,address] => address,address
2. Split the list into n columns, e.g., B_email_1, B_email_2, ..., B_email_n
3. Perform n LEFT JOIN steps where the join key is B_email_1 equals A_email, B_email_2 equals A_email, etc.
4. You will end up with n columns containing the location (e.g., USA, UK etc.); merge these into a single column
5. Optional: merge the previously split email address columns back into a single column, etc.
6. Cleanup: delete the columns that were generated along the way

Does this help?

Regards,

Nathanael

[Trifacta_Alumni · Accepted solution]

Hi again @Benjamin Sasin?,

So the root concept behind my solution is sound, which is to JOIN on the elements of the array individually. But a much better way to do it than I proposed above is simply to:

1. Copy the arrays into a new column
2. Convert one of the array columns into rows
3. Join as per normal

Much nicer!

Cheers,

Nathanael

[4d78bb8719d997eb9a26]

Thanks. I will try that.

For the first method: from my understanding the join in step 3 won;t be dynamic. So I can't predict how many joins I would have to have and so I cannot automate the process.

Your second method may be preferable. I'll give it a go,

[4d78bb8719d997eb9a26]

Hi Nathaneal,

Once I have done the join and then merged columns,

I end up with an "emails" column (the copy of the email array), which I then need to collapse back into a single row per unique array.

What tool do I use to group these rows back together?

[4d78bb8719d997eb9a26]

Okay I think I figured it out, first I had to extract unique values from the combined email array and also order them then I can combine them using group by.