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.