Fill in gaps in data

renee12345

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?

MarkSnodgrass

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.

GrantSmith

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:

COALESCE(`Customer ID`, `New Customer ID`)

renee12345

Solved it with group by tile!

MarkSnodgrass

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.