Daily data to monthly average data joining issues

KNIME Analytics Platform
1

I have knime data where I have created a Daily date from a variable ONSTREAM_DT and it runs the length of the budgeted process which is 120 months so that is now in a daily number. The first date however usually isn’t the first of the month.

I have a second tab in the excel sheet that has the monthly budget items. But I cannot get them to join properly.

My thought was that I would join the data, the result would be monthly data in the first of each month, then use a missing value node to fill in the remainderr of the month with the monthly average data. It hits the next month and repeats filling in the missing values with the new values for that month.

There are multiple entities in the data with 2 budget versions each in here.
When I unfilter the data it will be hundreds of Entities with multiple budgets. How do I get this done? Attached is the data in two tabs. One daily, one monthly with all the data. Thanks.
Data.xlsx (55.5 KB)

2

I ended up row filtering the two budgets to be separate tables. Then the join on dates worked for the first of every month. Then I used the missing value node and did a previous value for the handling of it.

1 Like
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.