Find last row of data with given status

Dear Community,
I have data about client ratings for 18 months in a row. Not all clients have data for all 18 months (i.e., if onboarded later of left us earlier). The ratings have 2 possible statuses: valid or aged. Each client in the dataset was both valid and aged during the 18 months, BUT we do not know when the rating turned from valid to aged.
Is there any way to find the LAST valid rating, no matter which month it was, using - maybe loops? Or other nodes?
Thank you!

Hello @versatilka
If I understood correctly your challenge; you can test with a ‘Moving Aggregation’ node (‘Forward’ option and ‘Resolve missing’ activated). All ‘missing’ results can be tagged as ‘aged’

You would probably have to work in loop by client.

For further help, you should provide some sample/mock data.

BR

You could also flag the transition point by using the lag node to drop down the previous value onto the same row, then perform a formula test to compare the two columns. Column Expressions can also access the prior row value in a formula. I typically go this route to avoid loops for better efficiency.

Hi @versatilka

If your data is sorted by client and chronologically, then you could use a flow such as this:

image

The Counter Generation node assigns a sequential number to each row.

The Rule Engine is given a rule:

$status$= "Valid" => $Counter$

and append this as a new column “ValidIndex”

After the rule engine completes, all the rows with a Valid status will have a “ValidIndex” number next to them. For the other rows, it will be “missing”.

The GroupBy node is set to group by Client, and return the Maximum ValidIndex. This will result in a table where for each client it contains the highest “ValidIndex”.

The Joiner filters the output from the Counter Generation node, by joining on:

Client = Client  
Counter =  max(ValidIndex)

This will return you the rows from the original table that contain the latest Valid status for each client.

2 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.