Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Trying to identify missing dates

Deano478
12 - Quasar

Hi guys,

 

I'm currently facing an issue with a task regarding identifying missing dates I feel should maybe not be this hard.

 

Essentially I have a calendar below which contains all the dates starting from 2023-01-02 up until today 2023-01-29. I then have a second dataset that contains a list of emails and the dates in which someone completed an MS form. What I'm trying to do is figure out what dates are missing from the Ms Forms data set based on the Calendar data and the generate the missing rows

 

This sounds like something that should be easy but I'm just drawing a big blank.

 

Cheers for any input and or help in reaching a solution.

4 REPLIES 4
aatalai
14 - Magnetar

@Deano478 have you tried using the ts filler tool under time series that might be your best bet. will give you a list of all dates between two dates and whether or not the are in the dataset. Let me know if that helps?

alexnajm
17 - Castor
17 - Castor

If you are just comparing the two datasets and their dates, a Join tool should work well! Otherwise I'd use a Multi Row Formula to fill those nulls - see attached for both.

Deano478
12 - Quasar

Hey @alexnajm cheers that worked my follow on question is also that I need to just generate the date of year number for each person for each date so like an id with jan 1 being 1 and December 31 being 365. I hope tats no too much trouble my apologies. 

alexnajm
17 - Castor
17 - Castor

@Deano478 you can use %j in a DateTime function then to get day of the year! DateTime Functions (alteryx.com)

 

DateTimeFormat([Date],"%j")

Labels