Filter batches of records based on latest entry

Greetings,

I have a data set that looks like this:

ACCOUNT| ENTRY ID | COMMENT | ENTRY DATE
Account 1 | 1 | Lorem Ipsum…| 01/01/2020
Account 1 | 2 | Lorem Ipsum…| 03/02/2020
Account 1 | 3 | Lorem Ipsum…| 04/05/2020
Account 1 | 4 | Lorem Ipsum…| 5/11/2020
Account 2 | 1 | Lorem Ipsum…| 01/08/2020
Account 2 | 2 | Lorem Ipsum…| 02/12/2020
Account 2 | 3 | Lorem Ipsum…| 03/15/2020
Account 3 | 1 | Lorem Ipsum…| 04/01/2020

I am trying to extract only the latest so my data appears like this:

ACCOUNT| ENTRY ID | COMMENT | ENTRY DATE
Account 1 | 4 | Lorem Ipsum…| 5/11/2020
Account 2 | 3 | Lorem Ipsum…| 03/15/2020
Account 3 | 1 | Lorem Ipsum…| 04/01/2020

I have created a loop so each account is being treated individually, I have sorted the ENTRY ID as Descending so the largest value is on top, I’m just not certain how to take it the rest of the way. Any help would be appreciated.

Thank you.

Signed,
John

Try this:

23507_pigreco.knwf (13.7 KB)

The first nodes was created to avoid to rewrite table according your example.

1 Like

Greetings,

Thank you for the workflow, I attempted the Reference Row Filter and it wouldn’t filter it for some reason. So I did a Join with the GroupBy and did a Rule-based Row Filter with Date = Max(Date) and that did it.

Signed,
John

2 Likes

Hi there,

don’t think this is good approach. You shouldn’t have your data as String type cause then comparison (maximum calculation) doesn’t have to be right (add 6/12/2019 for Account 1 and see). Use String to Date&Time node to have your Entry Date column as Date. Second problem that can appear is after GroupBy node in Reference Row Filter node. If maximum date in one account is same as non maximum date in another account you will have that included as well. This can be solved with Joiner by having both Entry Date and Account as joining criteria columns. Anyways I would simply use Duplicate Row Filter node after Entry Date is converted to Date. Include Account column only and on Advanced tab specify that you want to remove duplicate rows and include ones where Entry Date is maximum.

Br,
Ivan

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