Skip to content

    • Magic ETL

    Magic ETL

    Fill in gaps in data

    Member
    in Magic ETL

    I have rows of data that look like this:

    Customer: ID:

    12 NULL

    12 NULL

    12 NULL

    12 NULL

    12 NULL

    12 56

    How do I fill in the above rows with the customer ID, 56, Using ETL?

    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 can do this by first adding a filter tile and filter to where id is not null. Then add a group by tile and group by max id. This should give you one record of 12 and 56. Next, add a join tile and join your original input dataset with the group by tile and join on the customer column. Finally, add a formula tile and use ifnull to replace your id from the input dataset with the id group by tile.

      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    Answers

    • Coach

      Group by your ID field, select the max customer ID value then join it back to your original dataset based on your 12 ID field. Then do a formula tile to choose either the Customer ID or the new Customer ID for your Customer ID field:

      1. COALESCE(`Customer ID`, `New Customer ID`)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      Solved it with group by tile!

    • Coach
      Answer ✓

      You can do this by first adding a filter tile and filter to where id is not null. Then add a group by tile and group by max id. This should give you one record of 12 and 56. Next, add a join tile and join your original input dataset with the group by tile and join on the customer column. Finally, add a formula tile and use ifnull to replace your id from the input dataset with the id group by tile.

      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    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