Duplicate Multiple Rows for Missing Dates

[maier_alexander_MO]

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

[joshuaburkhow]

Hi @maier_alexander_MO 

There happens to be a tool created especially for this challenge around creating rows. It's called Generate Rows. I have attached the sample workflow for this tool to give you some ideas on how it works. Hopefully this is what you are looking for 🙂 

[benakesh · Accepted solution]

Hi @maier_alexander_MO ,

sort ->  Multi row ->  generate rows will   produce  the expected output .  Let me know if this helps .

[RolandSchubert · Accepted solution]

Hi Alex,

I added the next [Reporting_Date] to each rows (if there is a next reporting day,m so currently only for July, and generated the days between the reporting dates and between reporting date and today using the Generate Rows tool. Does this help? Workflow is attached.

Best regards

Roland

[maier_alexander_MO]

Dear all,

first of all thank you for the time you invested in my problem!

@joshuaburkhow: Those examples did help a lot, I now have a better understanding of the tool!

@benakesh: Thank you very much, the solution works greatly. I also played around with it a bit and learnt quite a lot.

@RolandSchubert: The solution was great and I will use it slightly different. I sorted for "Account" which is a unique number and then used "Account" as well to group in the Multi-Row Formula. I can actually also use the same setup for another input file which i receive on a weekly basis 🙂

This is such an awesome community, thank you so much for the quick help!

Kind regards, Alex