REMOVE DUPLICATE AND GET CORRECT DATA IN KNIME
Hello -
May I ask how can I get the correct line for the 3 cases below with duplicate IDENTIFIER?
In case 1, I need to retain the first line because in transaction date, the other two was posted or reversed last October and unique one is september.
In case 2, different to case 1 wherein there is no same month but the unique one should always be the latest month posted hence need to retain last line.
In case 3, the transaction date has the same month which is July however the one should be retained is the last line because it has the unique code (column code) which is 12345, both lines have 54321 code.
Hope you can help me figure out how to get this and ensure all of these 3 cases will be considered in every run of my workflow. Thank you!
| Case 1 | | | | | |
| IDENTIFIER | Type | Code | Company Name | Transaction Date | |
| ABC144896 | Posted | 12345 | ABC | 09/30/2024 | RETAIN |
| ABC144896 | Reversal | 12345 | ABC | 10/13/2024 | REMOVE |
| ABC144896 | Posted | 12345 | ABC | 10/24/2024 | REMOVE |
| | | | | | |
| | | | | | |
| | | | | | |
| Case 2 | | | | | |
| IDENTIFIER | Type | Code | Company Name | Transaction Date | |
| EFG144896 | Posted | 67890 | EFG | 08/31/2024 | REMOVE |
| EFG144896 | Reversal | 67890 | EFG | 09/30/2024 | REMOVE |
| EFG144896 | Posted | 67890 | EFG | 10/31/2024 | RETAIN |
| | | | | | |
| | | | | | |
| | | | | | |
| Case 3 | | | | | |
| IDENTIFIER | Type | Code | Company Name | Transaction Date | |
| HIJ320597 | Posted | 54321 | HIJ | 07/30/2024 | REMOVE |
| HIJ320597 | Reversal | 54321 | HIJ | 09/25/2024 | REMOVE |
| HIJ320597 | Posted | 12345 | HIJ | 07/30/2024 | RETAIN |