Hi all,
I have a rather tricky problem to solve which requires to add multiple rows for missing dates. Therefore I am hoping that maybe you already had as similiar issue or could at least help me which tools I should use?
Background: I am have a monthly report (last business day of the month) which shows account balances of different accounts in local currency. For this, I already did the aggregation and the calculation to EUR as can be seen in the workflow attached as a picture.
Goal: The aggregated data (see attached Excel file of the current state of the data) shows the account balance only for the end of the month. I would require each line item to be duplicated daily until the next reporting date is available, or if this is non-existant, until today.
Example: Currently I have the input files from 31/07/2019 and 30/08/2019. Today (24/09/2019) I would require to run the workflow and receive the following results:
- The line items from 31/07/2019 are duplicated for each day until 29/08/2019
- The line items from 30/08/2019 are duplicated for each day until Today (24/09/2019)
It is also important that weekend days are duplicated as well - so every day for every account should have one row.
The example file is of course only an extract of the total data but it shows the principle and the current state of my data. Every month, new accounts could be included or deleted, so sadly the account number cannot be used in the formula.
Do you have any ideas on this issue? Thank you so much for your help in advance!
Kind regards, Alex
