Collapse table and create new columns

[pranabjha]

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

[mlauber71]

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

School of duplicates - and how to deal with them - H2 version – mlauber71

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

Duplicate Row Filter – ana_ved

[Snowy]

Hi @pranabjha
Here is one potential solution…

Here I take your data (Table Creator) and use the Pivoting node to get the following output:

I then use the Column Resorter node to make sure the “date” columns are in the correct order (oldest date to newest date)… You could use the Reference Column Resorter if you didn’t want to sort these manually.

Finally, I use the Column Aggregator node to select the first and last value from each row:

The output:

And from here you could use the column filter node to get rid of the unwanted columns…

[morpheus]

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

BR

[HansS]

Hi @pranabjha

Or you try this solution. Its uses a GroupLoop (not always handy when dealing with large data sets) but it works. collaps_table.knwf (53.7 KB)

gr. Hans

[Daniel_Weikert]

I agree with @morpheus. Seems to be the easiest, and straight forward but maybe I am missing something?

[Snowy]

It definitely is.

This is one of the reasons I really like KNIME (“the photoshop for analytics”) is because there is never 1 solution… Always many solutions and you can learn from each one

[pranabjha]

Thank you @morpheus, @HansS and @Snowy. That was very quick. Much appreciated. The sorting + group by worked nicely. I had actually tried it earlier, but did not do aggregation properly. Actually, I did not know that I could use the same column twice for aggregation.

Much appreciated!!