Magic ETL

Magic ETL

Joining 3 different datasets to match zip codes

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:

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

Output:

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

Thank you

Tagged:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

    1. 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

  • 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!**
  • What would the join key be for the second join tile?

  • 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

    1. COALESCE(`dataset1.zip`, `dataset2.zip`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Incredibly helpful, thank you.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In