Hello, I have a table that I want to collapse. I am not able to exactly figure out how to accomplish this. I tried using the the ‘Group By’ nodes, but not able to do it yet. I am hoping it is a simple solution for my experienced Knime’rs. Hope one of you can help me here. Input table: Country Company Product Group Date Price ITALY M JA 12-02-13 1100 ITALY M JA 12-02-13 1100 ITALY M JA 12-02-14 1000 ITALY M XE 03-12-12 1200 ITALY M XE 03-12-13 190 ITALY M XE 03-12-18 190 Desired output table is: Country Company Product Group EarliestPrice LatestPrice ITALY M JA 1100 1000 ITALY M XE 1200 190 In the output table, each row is unique for a combination of Country, Company and Product. The ‘EarliestPrice’ and ‘LatestPrice’ are the ‘Price’ values from the input table corresponding to the earliest and latest ‘Date’ respectively. Please point me to a solution if it already exists. Thank you in advance. Best, Pranab

Hi, you can try the group by node with first and last aggregation setting. But first ensure correct data order. BR

Collapse table and create new columns

mlauber71 June 25, 2021, 2:48pm 2

One idea could be to use a H2 SQL database and Window functions like in this article.

Or you might want to try the new duplicate row filter which can do things like first and last and other things

1 Like