Skip to content

    • Magic ETL

    Magic ETL

    Joining 3 different datasets to match zip codes

    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:

    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

    • 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!**
    • Member

      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!**
    • Member

      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

    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

    Quick Links

    Categories