Using Rank node to Obtain the most recent record using a date field
This is a build-up over the post from Monissha Agil
Below are the instructions of how from a table with different states of a record you can obtain the latest modification based on the date of the modification and other categorical variables such as type of the record.
Use case; Pick the amount of latest record of for an specific organization and type of liability
As it can be observed on the first organization there is different amounts for the same type of liability, only been different on when they were updated, we are inserted into obtain the most recent.
The Rank Node definition;
Here is where the real trick happens, as in this case we are interest in just the newest record we sort the rank descending and limit the threshold to a constant 1, which will return the top record of the descending list.
Then equally important is to define what are the partition for the rank to be executed, as in this case we are interested in the latest record for an specific organization AND type of liability, both are required, this is critical to define it correctly.
Using the organization that had most duplicated we have a final clean one record per organization and type using the latest update of the combinaton