Generating rows between two date that differ more than a day

[Brayndasilva]

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?

[danilang · Accepted solution]

hi @Brayndasilva 

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

[atcodedog05]

Hi @Brayndasilva 

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 : )