Alteryx Designer Discussions

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

Inserting new records to complete a time line

jbooker1
5 - Atom

I have a data set that is based on transactional activity that is published to Tableau Service. Not all items are transacted daily leaving gaps in the date sequence, so I would like to insert a row to represent the missing day(s) and carry down the value from the previous recorded transaction so when the data is published I can have a consistent range of dates for trending.

3 REPLIES 3
NicoleJohnson
14 - Magnetar
14 - Magnetar

This is a perfect use case for Multi-Row tool (to find the next date in the list) and the Generate Rows tool (to generate a record for each day in between the current record's date and the next one). You might want to sort your dates first, before the Multi-Row tool, and look out for Group By other fields if you need to account for that in your real source data. See attached for example! 

 

Inserting Missing Dates.JPG

Cheers!

NJ

jbooker1
5 - Atom

Now I am realizing I want to extend the timeline to the current even if the last transaction has not posted yet. For example the last transaction was posted on 10/31/21 so I would like to have the timeline continue to today with the same value until a new transaction is posted. Can you use the same tool?

NicoleJohnson
14 - Magnetar
14 - Magnetar

Absolutely! You'll just need to modify the formula you use for the "NextDate" field in the Multi-Row Formula tool... original version shows a Null value for the last row, since there isn't a next row to look at. Instead, if you use an IF statement to tell you when you have reached the last row, you can use DateTimeToday() to tell it to stop when it reaches today.

 

If IsEmpty([Row+1:Transaction Date])
Then DateTimeToday()
Else [Row+1:Transaction Date]
Endif

 

Give that a try! 

Cheers,

NJ

Labels