Splitting Column Headers and unpivoting them to Rows

Hi Everyone!

I have a dataset to which the columns might vary month to month.
It can vary if there are additional “Plants” being added or a new “Region”.
I need to draw the data from the column headers.

The excel that I would receive would be like this.
The Plant Codes (DC) are grouped by Regions (eg. US, Mexico).
So if there are multiple plants in a Region, it will be reflected as “US Vilia - DC 1001/ 1009”.
The “Region” is always the 1st word and the “DC Plant Codes” will always be at the end and typically separated by “/”.

SKU US Vilia LT2 -
DC 1001
US Jones LT2 - DC 1020 US Vilia LT2 -
DC 1001/ 1009
US Jones - DC 1020 CAN -
DC 1004/ 1014/ 1020
Mexico -
DC 1005/ 1013
HK Virtual DC
W002
Taiwan DC
W005/W700
ABC Logistics Pte Ltd
W601
GHTE Virtual DC
W602
GHTE Virtual DC
W605
A 55 64 - - 20 18 0 24 20 0 16
B 83 72 - - 20 18 0 42 21 0 21
C 83 72 - - 20 18 0 42 21 0 21

I need to unpivot it so that I can derive information from the headers for mapping later on.
So I need to find a way to unpivot the above table to the below table.

SKU Region DC Qty
A US 1001 55
A US 1020 64
A US 1001 -
A US 1009 -
A US 1020 -
A CAN 1004 20
A CAN 1014 20
A CAN 1020 20
A Mexico 1005 18
A Mexico 1013 18
A ABC W002 0
A ABC W005 24
A ABC W700 24
A ABC W601 20
A GHTE W602 0
A GHTE W605 16

I am assuming I should use some sort of regex split but I am not familiar with it :frowning:
I have struggling with pivoting and unpivoting and resorted to just creating a table using Table Creator node to just input the values manually. I am very sure there must be an easier way to do this :sob:

Please help!! Thank you!!

Hello @neekstressed
Take a look on this workflow. It can be applied to your use case.

Let us know if some clarifications are needed.
BR

3 Likes

Hi @gonhaddock

I thought of the workflows in this thread you just mentioned as possible solutions to @neekstressed’s problem and was looking for it among my records but couldn’t find it. Thanks for thinking of it and sharing it here :smile: :+1: !

Have a nice day ahead !
Best
Ael

1 Like

Hi @aworker
Some challenges in forum have been already covered somehow in other similar topics. It’s hard to find them by topic name for me as well. My trick is that I keep the topic link in the description of the workflow itself…

In the other hand, I think that the intention of the forum is to learn, rather than a consultant service. Providing the result straightaway in a workflow doesn’t allow to the poster the option to fully explore the functions, logics and to develop the KNIME mindset by building his own solution.

BR

1 Like

Hi @gonhaddock

Indeed, as you can see from the names of my workflows they have the title of the thread and even the date when they were posted but some times this is not even enough because the thread titles are not always so explicit or meaningful lol :sweat_smile:

Cheers
Ael