Need Help in How to find the Missing Dates

[LessyYam]

Hello,
I need to find missing the missing dates which excludes the weekends (Sat and Sun).

For Example,

Acc Date	Process Date
01/01/2024	01/02/2024
01/02/2024	01/03/2024
01/03/2024	01/04/2024
01/05/2024	01/06/2024
01/08/2024	01/09/2024
01/09/2024	01/10/2024
01/11/2024	01/12/2024

As the above example contains the date for January 2024,
As the weekend dates are not included, the missing dates are 01/04/2024 and 01/10/2024.

I want Alteryx workflow to give me the result as missing dates 01/04/2024 and 01/10/2024

The above example are for every day dates.




I also need to find the missing dates for the dates that comes in twice a week.

For Example,
Dates only comes in Wednesday and Thursday.

Acc Date	Process Date
01/03/2024	01/04/2024
01/04/2024	01/05/2024
01/10/2024	01/11/2024
01/11/2024	01/12/2024
01/18/2024	01/19/2024
01/24/2024	01/25/2024
01/25/2024	01/26/2024

So in above example, The dates only comes on Wed and Thru, and the missing date is 01/17/2024.

 I want Alteryx workflow to give the result as missing dates 01/17/2024.

[caltang · Accepted solution]

I believe this solves your first query:

I will share the full workflow, just wanted to show you this first.

[caltang · Accepted solution]

As promised, this is Part 2:

[caltang]

Both solutions are quite dynamic I would say. It's based on the premises which you have requested. 

Part 1:

1. NOT Weekend, only Weekday missing dates.
2. Dates in January are used - you can adjust it to whatever time period in the Generate Rows tool.
3. You can adjust the final filter to include or exclude weekends should you change your mind.

Part 2:

1. The dates follow a pattern. In this example, only WEDNESDAYS and THURSDAYS of that period.
2. So, the logic is the same - just get the dates from start to end, then get only Wednesday and Thursday as per pattern (grouped by Summarize tool and using concat).
3. If the pattern changes to Thursday and Friday only, the Summarize tool will automatically adjust.
4. The pattern follows the field - therefore, if your concat changes, the join also changes - which means it will grab dates and join them if they exist. If they do not exist, they will exit on the "Right" side of the Join - which is what you want.

Hope this helps.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

[LessyYam]

Thank You Caltang, This is perfect as I needed.
But I am having a query that, If these two data are provided in a two different sheet in an Excel file, then how the workflow gonna be look like?
I have attach a Demo Excel file for you, That Excel file contains 3 sheets,(Summary, Daily, Weekly ).
I need an output like Summary tab.
If you can try to make an output like the Excel file I provided,
It will be appreciated.
Thank You Again.

[aatalai]

The TS filler tooler is handy at identifyingmissing dates I would suggest taking a look at that

[caltang · Accepted solution]

Like this:

To render it out, please change this part and path:

[caltang]

@LessyYam if it helps, can you kindly mark as accepted the posts I've made above for you? All of them answered your queries and more.

Click on the Green Button on my replies if they have helped. It will help others find the solution as well should they face the same issue/query as you do.

[LessyYam]

Hello Caltang,
Thank You for providing me the solutions.
The solution you send are perfect.

But there are things I wanted to know.
Like, If I am having multiple sheets with different feeds, So Do I need to make different workflows for different feeds.
For example,
The Excel file contains 6 sheets with different feeds. So do I need to make different workflows for different sheets?


I have attached an Excel for you. Please tell me how to process all feeds in single workflow and output should be same result as Summary sheet.

[caltang · Accepted solution]

Made one for you to accomodate all your requests.

Again, please mark as accepted if it helps.