Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Generating rows between two date that differ more than a day

Brayndasilva
7 - Meteor

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:

 

datexy
2021-01-2001
2021-01-2101
2021-02-1012
2021-02-1112
2021-02-1212
2021-03-0211

 

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?

2 REPLIES 2
danilang
19 - Altair
19 - Altair

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

danilang_0-1630939402048.png

 

Dan

 

 

atcodedog05
22 - Nova
22 - Nova

Hi @Brayndasilva 

 

Here is my take on it. A slightly different approach.

 

atcodedog05_0-1630940721790.png

 

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

 

Labels