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

Reply
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
Looking for the latest Dojo Community solutions? Click on the "NEW SOLUTIONS' widget title or go to https://dojo.domo.com/solutions Click here!