How to get related data in Group By

[IrynaK]

I have a list of text IDs, which I need to sort and get the maximum (eg. LA123, LA12). In order to get LA123 as a maximum, I made a number of string manipulations and finally I have two columns, one that contains the true id (I need it for matching data from other tables), and the manipulated id to get the maximum:
True ID New ID Status
LA12 LA0012 Taken
LA123 LA0123 New

Now I am doing the Group By the new ID and get my LA0123, which is correct. However, I am not able to find a solution how I can get the rest of the row data associated with LA0123. My goal was to mark the row with LA0123 somehow but the data that I need to proceed with is LA123 NEW. The Group By node does not let me set the new column, nor does it allow me to pull associated data in a way that I can use for further matching.

Hopefully I am making sense.

Looking forward to you smart people for answsers

[mlauber71]

The idea would be to keep a RowID or artificial ID together with the New ID and then join back all lines that are associated with it.

We had a similar discussion involving duplicates where instead of removing them one could keep the latest one:

Remove rows with duplicate values

I set up a workflow to demonstrate how this could be done use group by to calculate how many duplicates there are (note: KNIME should introduce a generic COUNT(*) function - I had to use a variable) if the count is larger then 1 it is a duplicate left join it back to the original data sort the data by ID and other variables if you want to keep one of the duplicates use the LAG column to identify which line is a 2nd, 3rd occurrence of a duplicate make a rule to keep just a single line of each …

[IrynaK]

Thank you!
I added Constant Value Column ‘Flag’ thus flaging the grouped by rows and then rejoined to the main table.
Thank you for the idea!