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

user04397

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.

 

ID	Date	Stage	Activity	Rank
123	01-Jan-18		Email	1
123	02-Jan-18		Call	2
123	03-Jan-18	Stage 1		3
123	04-Jan-18		Email	4
123	05-Jan-18		Email	5
123	06-Jan-18		Call	6
123	07-Jan-18	Stage 2		7
123	08-Jan-18		Call	8
123	09-Jan-18		Call	9
123	10-Jan-18	Stage 3		10
456	04-Jan-18		Email	1
456	05-Jan-18		Email	2
456	06-Jan-18	Stage 2		3
456	07-Jan-18		Call	4
456	08-Jan-18		Email	5
456	09-Jan-18		Call	6
456	10-Jan-18	Stage 4		7

 

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

 

ID	Date	Stage	Activity	Rank
123	01-Jan-18	Stage 1	Email	1
123	02-Jan-18	Stage 1	Call	2
123	03-Jan-18	Stage 1		3
123	04-Jan-18	Stage 2	Email	4
123	05-Jan-18	Stage 2	Email	5
123	06-Jan-18	Stage 2	Call	6
123	07-Jan-18	Stage 2		7
123	08-Jan-18	Stage 3	Call	8
123	09-Jan-18	Stage 3	Call	9
123	10-Jan-18	Stage 3		10
456	04-Jan-18	Stage 2	Email	1
456	05-Jan-18	Stage 2	Email	2
456	06-Jan-18	Stage 2		3
456	07-Jan-18	Stage 4	Call	4
456	08-Jan-18	Stage 4	Email	5
456	09-Jan-18	Stage 4	Call	6
456	10-Jan-18	Stage 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.

BlueRooster

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:

Here's the details for each step:

 

 

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.

BlueRooster

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:

Here's the details for each step:

 

 

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

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

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