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

Populate records for missing dates

aokalabak
5 - Atom

Dear all,

 

I have an excel file with departure data, like 

 

DepartFromDepartDateTotalCount
LA01.03.20182
LA04.03.20184
LA07.03.20183

 

for missing dates, I want to populate rows with TotalCount=0, final data will be like 

 

DepartFromDepartDateTotalCount
LA01.03.20182
LA02.03.20180
LA03.03.20180
LA04.03.20184
LA05.03.20180
LA06.03.20180
LA07.03.20183

 

I tried join and union tools, prepared another excel sheet containing missing dates and merge two files but somehow I couldn't find a way. 

 

Is there a way to populate values for missing dates?

 

Thanks in advance

Ahmet

4 REPLIES 4
LordNeilLord
15 - Aurora
Hey @aokalabak

Here's how to get the answer:

1. Using summarize tool, group by departfrom and return min & max departdate
2. Use generate Rows tool to fill all the dates between min & max
3. Join this back to your original file using date as the join criteria
4. Union the left (or right depending on how you did the join) and join output to get a complete list
5. Use the formula tool (or data cleanse) to turn the null Total Count into zero

Let me know if any of that doesn't make sense

Neil
aokalabak
5 - Atom

Thanks @LordNeilLord, this worked :)

 

Kind regards,

Ahmet

BenMoss
ACE Emeritus
ACE Emeritus

Depending on the complexity of your datasource this is (given the sample you shared) possible with just one tool, the 'TS Filler' tool, which is part of the 'Time Series' toolkit.

 

The tool is designed to fill in missing dates.

 

Example attached.

 

Ben

kpombo
7 - Meteor

Can you post a workflow example for this? I am having trouble getting the generate rows tool to work.

Labels