Magic ETL - filling values in a column based on other values in the same column

user04397
user04397 Member
in Magic ETL

Hi there,

 

Ran into a bit of a roadblock with my MagicETL. I was able to get to a certain point using the Rank & Window function but I can't figure out how to do the next step I need.

Below is an example of where I'm at after combining two tables and partitioning them based on ID and sorting them by date.

 

IDDateStageActivityRank
12301-Jan-18 Email1
12302-Jan-18 Call2
12303-Jan-18Stage 1 3
12304-Jan-18 Email4
12305-Jan-18 Email5
12306-Jan-18 Call6
12307-Jan-18Stage 2 7
12308-Jan-18 Call8
12309-Jan-18 Call9
12310-Jan-18Stage 3 10
45604-Jan-18 Email1
45605-Jan-18 Email2
45606-Jan-18Stage 2 3
45607-Jan-18 Call4
45608-Jan-18 Email5
45609-Jan-18 Call6
45610-Jan-18Stage 4 7

 

I would like to fill in the blank "stage" cells with the stage of the latest stage in the series. Example:

 

IDDateStageActivityRank
12301-Jan-18Stage 1Email1
12302-Jan-18Stage 1Call2
12303-Jan-18Stage 1 3
12304-Jan-18Stage 2Email4
12305-Jan-18Stage 2Email5
12306-Jan-18Stage 2Call6
12307-Jan-18Stage 2 7
12308-Jan-18Stage 3Call8
12309-Jan-18Stage 3Call9
12310-Jan-18Stage 3 10
45604-Jan-18Stage 2Email1
45605-Jan-18Stage 2Email2
45606-Jan-18Stage 2 3
45607-Jan-18Stage 4Call4
45608-Jan-18Stage 4Email5
45609-Jan-18Stage 4Call6
45610-Jan-18Stage 4 7

 

Does anyone know if this is possible in Magic ETL, and if so, what's the best way of doing this?

 

Thanks in advance.

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    Got it! Took a bit of thinking but here you go. I took the example dataset you gave me as the "Entry" and was able to do it with the following steps:

    image.png

    Here's the details for each step:

     image.png

    image.png

     

    image.png

    image.png

    image.png

    image.png

    Always interesting trying to do things like this in ETL.

     

    Best of luck!

    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    Got it! Took a bit of thinking but here you go. I took the example dataset you gave me as the "Entry" and was able to do it with the following steps:

    image.png

    Here's the details for each step:

     image.png

    image.png

     

    image.png

    image.png

    image.png

    image.png

    Always interesting trying to do things like this in ETL.

     

    Best of luck!

    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • user04397
    user04397 Member

    Thank you so much for this, i managed to do it another way over the weekend but your way is definitely cleaner I'll keep this in mind when I run into the same situation again.

  • user020184
    user020184 Member

    How to do if there is just one dataset involved and I want to fill down a column just as we do in Excel?

Categories