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

emmanuelfabre_1
emmanuelfabre_1 Member
edited December 2023 in Magic ETL

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!

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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.

  • emmanuelfabre_1
    emmanuelfabre_1 Member

    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!




Categories