Fill in gaps in data

renee12345 · September 9

I have rows of data that look like this:

Customer: ID:

12 NULL

12 56

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

MarkSnodgrass · September 9

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 · September 9

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 · September 9

Solved it with group by tile!

MarkSnodgrass · September 9

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.

Fill in gaps in data

Best Answer

Answers

Categories