How can I update row values for col1 based on the common ID in col2

emmanuelfabre_1 · 2023-12-28T04:15:41+00:00

There was an error rendering this rich post.

How can I use MagicETL to update the rows with NULL Source values to have their relevant Source value, based on ID?

Starting Table
———————-
Source | ID | RankCol
———————-

plant | 100 | 1
NULL | 100 | 2
animal | 101 | 1
NULL | 101 | 2

Desired Resulting Table
———————-
Source | ID
———————-

plant | 100 | 1
plant | 100 | 2
animal | 101 | 1
animal | 101 | 2

Any help is GREATLY appreciated!

Find more posts tagged with

Magic ETL

Accepted answers

MichelleH

@emmanuelfabre_1 You can do this using a Group By tile by setting ID as the Group by field and calculating the first non-null value of Source. Then you can join the Group by tile back to the original dataset by ID and replace the original Source field with the one from the Group by tile.

All comments

MichelleH

emmanuelfabre_1

Hi, @MichelleH. Happy New Year!

I worked way too long on a few projects, and found a solution on this project. Unfortunately, I forgot to check this thread, as the solution came to me a few hours later. I used a Group by tile like you suggested.

Again, apologies for not replying sooner. The excitement of finishing the work week Thursday morning, in time for a long weekend, got the better of me.

Thank you very much!