Hello @ChineS
Just a small correction on my previous post: the join is always an outer join to match the template. At some point before the joiner, you will have to unpivot the current row in loop.
I have few examples already posted in the forum, let’s focus in 2 of them:
- Tis WF is a good example because the size of the report isn’t fix. The starting date rolls with the WF execution and the length (number of columns) can be any depending on the displayed projects (items), as in your use case.
Weekly data has to be aggregated on an irregular grouped bins as well, defined in 'Reporting Format ’ meta-node $Period$, increasing complexity.
The ‘Processed INPUT Data’ meta-node preprocess the data, and argues with ‘Basic INPUT Data’ about reporting logics, then populating: the length ($Life left (weeks)$), first efective financial day on Monday basis, reporting start (next Monday from workflow execution) ; shaping the reporting format.
You will find a an outer join within the loop to extend the columns all over the empty values; the result can be inspected in ‘OUTPUT 01’ end node
- This WF has a format support section for the loop (Sort Reporting Columns Ordered by Chronology). This is a good example on how to handle missing values. As you see it can handle null values in any column position.
The shaded joiner in “Missing Handling” can give you an insight; The ‘Sales Counts Report’ shows the results with the blanks()
In this case preprocessing is not needed, as the ‘Custom Report’ is predefined.
I hope this is clear enough.
BR