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

Reply
Highlighted
Visitor

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

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.

Tags (1)

Accepted Solutions
Black Belt

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

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.


All Replies
Black Belt

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

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.

Visitor

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

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.

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!