Using Rank node to Obtain the most recent record using a date field

[Former Member]

Hi,

This is a build-up over the post from monissha

http://scn.sap.com/docs/DOC-63775

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

The data;

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 View;

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.

The outcome;

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