Joining 3 different datasets to match zip codes

jrtomici · 2023-04-19T17:35:50+00:00

There was an error rendering this rich post.

Hi, I have 3 datasets each with a column containing zip codes. I want to use ETL to create a new dataset with 3 columns, all containing the zip codes from each input lined up.

Input:

Dataset 1        Dataset 2        Dataset 3
1                2                1
2                3                3
3                5                4
4                6                5

Output:

Thank you

Find more posts tagged with

Join

Magic ETL

Accepted answers

GrantSmith

You'll likely need to coalesce your dataset1 zip code and dataset2 zip code into a new field using a formula tile and then use that to join your dataset 3 zip code. This way it will pull in zip codes that don't exist in dataset1

COALESCE(`dataset1.zip`, `dataset2.zip`)

All comments

GrantSmith

You can use two join tiles with a full outer join to do this. Join tables 1 and 2 together then join the output of that to table 3 via an outer join.

jrtomici

What would the join key be for the second join tile?

GrantSmith

COALESCE(`dataset1.zip`, `dataset2.zip`)

jrtomici

Incredibly helpful, thank you.