Joining 3 different datasets to match zip codes

jrtomici
jrtomici Member
in Magic ETL

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:

D1  D2  D3
1       1
2   2
3   3   3
4       4
    5   5
    6

Thank you

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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`)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jrtomici
    jrtomici Member

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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`)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jrtomici
    jrtomici Member

    Incredibly helpful, thank you.

Categories