Hi,
I am trying to generate rows based on a gap found between two dates while keeping the original data from the first date. Consider the following set of data:
date | x | y |
2021-01-20 | 0 | 1 |
2021-01-21 | 0 | 1 |
2021-02-10 | 1 | 2 |
2021-02-11 | 1 | 2 |
2021-02-12 | 1 | 2 |
2021-03-02 | 1 | 1 |
What would a best practice be to only generate rows for the missing dates? These generated rows should have the same x and y values as the first date. So for example '2021-01-22' until '2021-02-09' would be 0 1.
I have been able to identify the start of the gap on the same row with a Multi-Row Formula tool which I then intended to use in some sort of condition to feed into the Generate Rows tool. Refer to the attached example workflow. I am not sure how to leverage the Generate Rows tool to achieve the desired result. Perhaps I don't even need to use a Multi-Row Formula tool?
Solved! Go to Solution.
You were close. The Generate rows needs to have the start and end dates on the same record and valid dates everywhere. Change it to this
IF DateTimeDiff([Row+1:date],[date],'day') > 1 THEN
datetimeadd([Row+1:date],-1,"days")
ELSE
date
ENDIF
If the gap to the next row is bigger than one day, add next day -1 as the gap. Otherwise use [date]
The Generate rows tool is configured like this
Dan
Here is my take on it. A slightly different approach.
1. In multi-row formula I am getting the next row date. For last row since there is no next row I am adding +1 day to it to get next date.
2. Using generate row tool to generate dates in between.
Hope this helps : )