Help Transforming Data for further Analysis

[maya29]

Hi I am working on transforming my input data for further analysis I am trying to achieve the following:
Goal:

with the current data:

I have tried using the unpivot but for some reason the numbers are coming out incorrect. Thanks in advance!

[rfeigel]

You’re more likely to get some help if you upload your data so potential helpers don’t have to retype it.

[maya29]

New
Date	Day	Product Category	forecast quantity	Actual quantity
01.12.2024	Sonntag	Pen	7	26
02.12.2024	Montag	Pen	6	30
03.12.2024	Dienstag	Pen	30	25
04.12.2024	Mittwoch	Pen	38	26
05.12.2024	Donnerstag	Pen	7	5
01.12.2024	Sonntag	Pencil	21	21
02.12.2024	Montag	Pencil	31	30
03.12.2024	Dienstag	Pencil	49	26
04.12.2024	Mittwoch	Pencil	37	25
05.12.2024	Donnerstag	Pencil	46	32

Current
Date	Day	total forecast [Quantity]	total Actual[Quantity]	Penforecast	Pencilforecast	PenActual	PencilActual
01.12.2024	Sonntag	28	47	7	21	26	21
02.12.2024	Montag	37	60	6	31	30	30
03.12.2024	Dienstag	79	51	30	49	25	26
04.12.2024	Mittwoch	75	51	38	37	26	25
05.12.2024	Donnerstag	53	37	7	46	5	32

[Stephane73]

Hi @maya29

To solve the issue, I made some adjustments to the workflow.

You were on the right direction by using the Unpivoting node. However, to achieve the desired result, I needed to create the Product Category column. This column is essential because it will be used as the pivot column in the next step.

After creating the Product Category column, I applied the Pivoting node to reorganize the data structure as required.

Let me know if you have any further questions or need clarification on specific steps!

Pencil.knwf (111.9 KB)

[maya29]

Thanks a alot for this. I am incorporating your suggestions now. I am a little stuck at the Column expressions portion because instead of 2 product category I used in my example date we have 5 different product categories. How can I go about using the 5 in the column expressions? Or do i require a another node. Thanks alot for your help!

[maya29]

For product category, i tried using the following:

regexReplace(column(“ColumnNames”),“(pen|book|pencil|eraser).*”, “$1”)

due to the mutiple product category. But it does work unfortunately.

[Stephane73]

@maya29

Can you provide data for the product category to customize the Column Expression node ?

[maya29]

Hi thanks. I was able to figure it out using if else-. But thanks for getting back to me!

[maya29]

Quick question. In creating the new Product Category, how would you handle it if say a space was there for e.g. Pen Forecast instead of PenForecast? Thanks!