Type Category MSU 1-Jan 2-Jan
Overall HK Original 1 2
Overall HK Confirmed 1.2 1.2
Overall HK Received 1.3 1.3
Overall HK OOS 1.4 5
Overall Hair Original 1 2
Overall Hair Confirmed 1.2 1.2
Overall Hair Received 1.3 1.3
Overall Hair OOS 1.4 5
Below is the result i need, how to do? can you let me know details steps?
Type Category Date Original Confirmed Received OOS
Overall HK 1-Jan 1 1.2 1.3 1.4
Overall Hair 1-Jan 1 1.2 1.3 1.4
Overall HK 2-Jan 2 1.2 1.3 5
Overall Hair 2-Jan 2 1.2 1.3 5
I converted the MSU values into columns at first. Then 2 loops: One to loop over date columns and second one to loop over every 4 rows of data (as there are 4 unique values in MSU column).
The Column Expressions creates the “Date” column based on current column name and puts the values for MSU columns from current column (of date columns).
Column Filter node excludes MSU and the current column (the latter is set in flow variables tab).
And finally the GroupBy node aggregates rows based on Type, Category and Date and uses “Sum” function on MSU columns (Type based aggregation).
P.S. Sorry! I forgot to delete the Math Formula (Variable) node. It does nothing in this workflow. (I was going to use the iteration number of the first loop but then I changed my mind)
yes this work for repeated values. Because I do use the row Id in the pivoting node. This does ensure that the original data table structure is recreated.
I reopen this this topic because I have a question about the Unpivoting Node. I have a table with two columns. in the first column are the labels and in the scond there are the values. This Looks like this:
You can see that there are dublicate values in the lable column but unique values in the value column. I want the unique values in the pivoting table in the column [JOB]FaultMemReportMirrorMemoryDTCByStatusMask.DTC so the result should look like this
if I got you right you don’t need to do any unpivoting but only use Pivoting node with no group columns, Label as pivoting column and Value as aggregation column with aggregation method List. Then use Ungroup node. That should do it
Hi and welcome
Looks like an unpivot operation. Have a look at that node.
Just to let you know. It’s always best to upload a sample file (excel, csv,… attached)
br
Thank you very much! I’ve thought about “groupby” and then “unpivot” by not sure how to get the data values correct for each line item - below I uploaded the file, would really appreciate if you could have a quick look at it. BR Fredrik
Regarding column “Year” and column “month” which is now in separate columns - is it possible to merge them info a single column called “date” and instead of the format 2021 Jan, 2021 Feb, 2021, Mar - show something like e.g. Jan-21. Feb-21 and Mar-21?