Need Rank to update in non-standard (?) way

jimsteph · April 1

I've got customer records with customer GUIDs, record dates, and member status. If the data for a specific customer looks like this:

Date	Status
1/1/2023	Active
2/1/2023	Active
3/1/2023	Inactive
4/1/2023	Inactive
5/1/2023	Active
6/1/2023	Active
7/1/2023	Active

I want to get a dense ranking that groups the status together, but if the status changes the ranking would update:

Date	Status	Dense Ranking
1/1/2023	Active	1
2/1/2023	Active	1
3/1/2023	Inactive	2
4/1/2023	Inactive	2
5/1/2023	Active	3
6/1/2023	Active	3
7/1/2023	Active	3

(Ultimately what I need is the date the status changed, i.e.: the January, March, and May dates.)

If I include the status in the partition it ends up grouping all the active together with a rank of 1 rather than reset it when the date changes.

Is what I want possible in Magic ETL? We don't have access to the Python tile, so that, unfortunately, is out.

ColemenWilson · April 2

If ultimately what you need is when the status changed, you can use a lag function to check the previous status. If the previous status is different than the current status, then flag that row as when the change occured. If the previous status is null or the same, then flag it as no change.

ETL Overview:

Rank & Window:

2. Add Formula:

3. Output:

ColemenWilson · April 2

If ultimately what you need is when the status changed, you can use a lag function to check the previous status. If the previous status is different than the current status, then flag that row as when the change occured. If the previous status is null or the same, then flag it as no change.

ETL Overview:

Rank & Window:

2. Add Formula:

3. Output:

jimsteph · April 3

That's a huge help, and solved the immediate problem. Thanks!

That said, I forgot to mention that I'd also like to have a field called StatusChangeDate that, for all of the groupings, would have the earliest date of the grouping:

Date	Status	Dense Ranking	Status Change Date
1/1/2023	Active	1	1/1/2023
2/1/2023	Active	1	1/1/2023
3/1/2023	Inactive	2	3/1/2023
4/1/2023	Inactive	2	3/1/2023
5/1/2023	Active	3	5/1/2023
6/1/2023	Active	3	5/1/2023
7/1/2023	Active	3	5/1/2023

Going forward I'll be implementing something that sets that field as the record is written, but I need to be able to add it to the history if at all possible.

Need Rank to update in non-standard (?) way

Best Answer

Answers

Categories