Compress a table

KNIME Analytics Platform
1

Hello, KNIME Community,

I was wondering if I could get some help.

This is the output of my previous node. It relates to individual assessments for patients on particular days.

|ID   |Date      |A|B|C|D|E|F|G|H|I|J|K|L|M|
|87973|2022-12-20|?|?|?|?|?|?|?|?|?|?|?|?|0|
|87982|2022-12-23|?|?|?|?|?|?|?|?|?|?|?|?|1|
|87982|2022-12-28|?|?|?|?|0|?|?|?|?|?|?|?|?|
|87982|2022-12-28|?|?|?|?|?|?|?|?|?|?|?|?|1|
|87982|2022-12-29|?|?|?|?|0|?|?|?|?|?|?|?|?|
|87982|2022-12-29|?|?|?|?|?|?|?|?|?|?|?|?|0|
|87982|2022-12-30|?|?|?|?|0|?|?|?|?|?|?|?|?|
|87994|2023-01-04|?|?|?|?|?|?|?|?|?|?|?|?|0|

I want to merge the rows so that each ID and Date have a single row with the same A-M columns and name columns. So the output should look like this:

|ID   |Date      |A|B|C|D|E|F|G|H|I|J|K|L|M|
|87973|2022-12-20|?|?|?|?|?|?|?|?|?|?|?|?|0|
|87982|2022-12-23|?|?|?|?|?|?|?|?|?|?|?|?|1|
|87982|2022-12-28|?|?|?|?|0|?|?|?|?|?|?|?|1|
|87982|2022-12-29|?|?|?|?|0|?|?|?|?|?|?|?|0|
|87982|2022-12-30|?|?|?|?|0|?|?|?|?|?|?|?|?|
|87994|2023-01-04|?|?|?|?|?|?|?|?|?|?|?|?|0|

I think that I have a convoluted way of doing this but I’m after a simple answer.

Once again, thanks to all you who apply your brains to this problem. I remain grateful for this and all the past work.

A

2

Hello @AAM,

use GroupBy node. Your grouping column is ID, First on Date and Maximum on A-M columns as aggregations.

Br,
Ivan

4 Likes
3

Thanks Ivan,

I knew it had to be easier than the spaghetti nodes I was creating! Haven’t had any joy using GroupBy before, but I see why now.

Your help, though apparently simple, has saved me hours from embarking on a learning curve to nowhere!
A

2 Likes
4

Hello @AAM,
a bit more generic/dynamic way could be using first unpivot (removing empty content rows) node followed by a pivot node.
It should allows you variability in colomn names as well as in the table dimension.
BR

1 Like