Union records from multiple fields into one

Hello All

Lets say I have data that has multiple fields that are similar across my dataset, for example:

|ID|Value|ID (#1)|Value (#1)|ID (#2) |Value (#2)|
-------------------------------------------------
|1 |   10|      4|        15|       2|        30|

How would I split the fields in such that I can iterate over the field and value, then append to ID and Value fields to get :

|ID|Value|
----------
| 1|   10|
| 4|   15|
| 2|   30|

Would be greatly appreciated
Thank you

You are looking to pivot the data without aggregation.

I will never understand why pivot tools in most platforms require aggregation during pivoting. I pretty much never want to aggregate the data myself. Giant pet peeve of mine…

In your case you may need to loop it through the process by the ID / Value pairing as well.

1 Like

This post has a simpler approach to pivoting without aggregation using “ungroup” after the pivoting node as well. It also pointed out that you don’t need to select any GroupBy columns in the pivot node (you can just ignore the warning) which was an important piece to the puzzle.

Thank you for your reply - I really do not know how the pivot works and it looks like the two post you replied with are doing what I would like in reverse. I really would not know how to loop while using the pivot node.

The “unpivot” node does the pivoting action in reverse. Unfortunately it would be a few days until I could mock something up for you since I am on the road. Hopefully someone else can put together a quick workflow solution for you in the mean time. If not, I will build you one when I am back in the office next week.

Hello @Ak_105
Other solution:

1 Like

Technically, it works for the scenerio I gave above, but what if the columns have mismatching records? For Example, ID (#2) has more data than ID (#1)? Apologies as I probably should have added that

I was able to borrow a laptop for a minute. I put it into a loop and used regex rename to handle the iteration rename issues, then filtered out the missing value rows.

UNION RECORD ROW LOOP.knwf (27.3 KB)

Also, welcome to the forum!

5 Likes

It worked! Thank you!!

1 Like

Definitely should. I just don’t understand why requesters do that. As I always say, “Help us help you. The more accurate you are in your information, the more accurate the solution will be”.

1 Like

Here is an alternate approach that gets to the same solution by looping the “paired columns” through Transpose in a series. Gets to the same place, but in a different order since it processes down columns instead of across rows.

UNION RECORD COLUMN PAIR LOOP.knwf (24.8 KB)

Not that you need another solution, but I found this approach intriguing as my brain immediately thought of looping through the column pairs that were clustered by relationship and had never tackled it. Always more than one way to get there in KNIME!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.