I’m a bit stuck, the previous implementation was to just extract a value each day that made filtering a lot easier.
I’ve also just started using KNIME a month ago with my colleagues having a 1 month headstart and they can’t really help.
I got the following data out of JDE E1 (already unpivoted):
All I can get is year and month when it comes to dates as E1 matches it with a work day calendar.
Now my issue is that E1 has cells 1-31 (WURU01-31) for each month, with unused cells being 0 (eg. February 30-31).
Weekends and holidays are also 0 so I’ll have to take those into account as well when it comes to counting weeks.
I’m trying to get the capacity per week, can’t use Chuck Loop with a interval of 7 because I got 31 “days” for every month.
I would need a solution to filter out the unneeded rows based on amount of days in a given month.
I could go an manually filter out the unneeded rows but I feel that I might have to create more reports in the future for different work centers so I’d like to have a more robust solution.
It is a little bit difficult to give a straight forward solution. Because think you should find the solution before you pivot the data.
Try to extract the week numbers with the Extract Data&Time Fields node
Filter in your base table all rows of days without an observation. Or alternatively you can create a reference table with the Create Data&Time Range node, which you join to your exported table.
Before you pivot your table do some data manipulation to ensure you have te information you need after pivoting.
We’ve found a solution that worked for us thanks to how KNIME works with invalid dates.
Using MathFormula mod($$ROWINDEX$$,31)+1 we’ve counted to 31 and constructed at date (we have year + month).
The first row in that table will always be 01.01.YEAR so it’s pretty robust.
Then used the String to Date&Time node to generate a KNIME Date.
This lead to easy to filter duplicates as KNIME generates the next smaller valid date when given an invalid input:
