I do have quantities on a yearly basis and need to transpose them to a monthly basis but only if “start of production” is <= the “YYYY-MM” column and only for the respective year of demand. So I am currently trying to split the quantities of col(Demand) into multiple “YYYY-MM” columns based on col(Year demand) and col(Start production). Is there any recommendation on how to best do this?
Many thanks in advance for your support - highly appreciated.
Isabell
For an example of my “input -> desired output” please look at the attached pictures below and/or the test data in knime Year-to-Month-Data.zip (14.7 KB):
may I rephrase your request, just in order to check if I understood it correctly:
You want to transpose the data only for the remaining months of a year after SOP (depending on the year of Demand. Meaning if SOP is in 2019 and Demand is in 2019, you take the remaining months of 2019, but if demand is in 2020, you take the full 12 months)?
You intend to divide the demand equally over the number of corresponding months?
I’m not super familiar with the date&time manipulation in KNIME (therefore my solution may not be the most efficient one), but at least it works.
In a first step, you need a couple of nodes telling you over how many months your demand should be spread. I.e. if the year of SOP < year of demand, take 12, else measure how many months are remaining in the year of SOP.
After that, you basically need to loop over each row of the table, calculate the monthly demand and then append a line where you increased the starting month by 1. In the end, you just pivotize the result as you’d do in excel, and that’s it, all the magic done…
I appended how I’d do it with your workflow, but as I said, it might not be the most efficient solution (at least for large datasets).
many thanks for your great support - very much appreciated!!
I’ve made minor adjustments (Year-to-Month-Data2.knwf (87.6 KB)) to the workflow you’ve kindly proposed & now it works exactly as I need it to be Perfect!
Perfect!