# Collapse table and create new columns **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [June 25, 2021, 2:39pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145 "2021-06-25T14:39:46Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![pranabjha](https://forum-cdn.knime.com/user_avatar/forum.knime.com/pranabjha/32/25362_2.png) [@pranabjha](https://forum.knime.com/u/pranabjha)\ **Post date:** [June 25, 2021, 2:39pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/1 "2021-06-25T14:39:46Z")
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 ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [June 25, 2021, 2:48pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/2 "2021-06-25T14:48:00Z")
One idea could be to use a H2 SQL database and [Window functions](https://forum.knime.com/t/sqlite-and-window-functions/31608/4) like in [this article](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164). > **[School of duplicates - and how to deal with them - H2 version – mlauber71](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_duplicates_school_of_db_h2~kyA_KJ2QUUgI7g61)** > > Dealing with duplicates is a constant theme with data scientist. And a lot of things can go wrong. The easienst ways to deal with them is GROUP BY or DISTINCT.… 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](https://hub.knime.com/knime/spaces/Examples/latest/02_ETL_Data_Manipulation/01_Filtering/08_Filtering_Duplicates~8c9oCfDpXfRx5bL6)** > > This workflow is based on the adult.csv data set. Try it out to: 1. Remove duplicates - keep the first or last appearance of the duplicates - keep the row of d… ---
**Author:** ![Snowy](https://forum-cdn.knime.com/user_avatar/forum.knime.com/snowy/32/963_2.png) [@Snowy](https://forum.knime.com/u/Snowy)\ **Post date:** [June 25, 2021, 3:04pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/3 "2021-06-25T15:04:55Z")
Hi @pranabjha Here is one potential solution… ![image](https://forum-cdn.knime.com/uploads/default/original/3X/c/e/ce7278ca7b9dcf8bee8a7adfd7f9288c5e53b3c9.png) Here I take your data (Table Creator) and use the Pivoting node to get the following output: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/6/8/68b9efa87345c8e416364ef30ac599f5de54e7eb.png) 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: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/d/2d5e6de2dcc4305f196907cb2fd45e48ec334080.png) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/f/af23de54cd16f7fa015809c01d6b6295d281368d.png) The output: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/8/1/814b393cfcbc53c17ee75d1539dd08043db3bf02.png) And from here you could use the column filter node to get rid of the unwanted columns… ---
**Author:** ![morpheus](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/ce73a5/32.png) [@morpheus](https://forum.knime.com/u/morpheus)\ **Post date:** [June 25, 2021, 3:11pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/4 "2021-06-25T15:11:45Z")
Hi, you can try the group by node with first and last aggregation setting. But first ensure correct data order. BR ---
**Author:** ![HansS](https://forum-cdn.knime.com/user_avatar/forum.knime.com/hanss/32/6979_2.png) [@HansS](https://forum.knime.com/u/HansS)\ **Post date:** [June 25, 2021, 3:17pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/5 "2021-06-25T15:17:55Z")
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](https://forum.knime.com/uploads/short-url/6lF5mypfpgtOxP8jclkVVyLvT14.knwf) (53.7 KB) ![Schermafdruk van 2021-06-25 17-20-15](https://forum-cdn.knime.com/uploads/default/original/3X/4/f/4fe2dcb0b88476919eccc0409fd5c14884cbed36.png) gr. Hans ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [June 25, 2021, 3:22pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/6 "2021-06-25T15:22:18Z")
I agree with @morpheus. Seems to be the easiest, and straight forward but maybe I am missing something? ---
**Author:** ![Snowy](https://forum-cdn.knime.com/user_avatar/forum.knime.com/snowy/32/963_2.png) [@Snowy](https://forum.knime.com/u/Snowy)\ **Post date:** [June 25, 2021, 3:45pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/7 "2021-06-25T15:45:22Z")
It definitely is. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/3/7/37085f49ba1bfea95a4893c35d55c57609a43d84.png) 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 🙂 ---
**Author:** ![pranabjha](https://forum-cdn.knime.com/user_avatar/forum.knime.com/pranabjha/32/25362_2.png) [@pranabjha](https://forum.knime.com/u/pranabjha)\ **Post date:** [June 25, 2021, 4:45pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/8 "2021-06-25T16:45:49Z")
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!! 🙏 ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [July 2, 2021, 4:46pm UTC](https://forum.knime.com/t/collapse-table-and-create-new-columns/34145/9 "2021-07-02T16:46:31Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.