Trying to identify missing dates

[Deano478]

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.

[aatalai]

@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 · Accepted solution]

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]

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]

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

DateTimeFormat([Date],"%j")