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
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
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 !
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.
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
!