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

Dynamic input to contain business day

janani_venkat
7 - Meteor

Hi!

 

I am trying to build a dynamic input path using formula but cannot figure out how to add business day - basically, if I run the file end of last week, I need to pick a file with WD15 in the input file path. How do I do that?

 

Attached is my workflow - can you please tell me if this possible?

 

Expected path from my workflow:

 

C:\Users\JV\2021\012021\Data Dump\Data WD 15 cut.xlsx

9 REPLIES 9
ddiesel
13 - Pulsar
13 - Pulsar

Hi @janani_venkat !

 

 

Can you clarify your objective? Are you looking to input all the files in the selected directory that are weekdays within the current month? 

janani_venkat
7 - Meteor

 Hi @ddiesel,

 

My folder would contain multiple files - one file per WD, so the data in the folder would be Data Dump WD1 through latest WD. When I run this today, I would need it to pick the latest WD data saved (i.e., WD15 as today for Feb ME)

 

in my original post - the WD xx cut - xx would be the work day - hope below explains:

 

Files in the folder:

C:\Users\JV\2021\012021\Data Dump\Data WD 1 cut.xlsx

C:\Users\JV\2021\012021\Data Dump\Data WD 2 cut.xlsx

C:\Users\JV\2021\012021\Data Dump\Data WD 3 cut.xlsx

C:\Users\JV\2021\012021\Data Dump\Data WD 4 cut.xlsx

....

 

Thanks!

ddiesel
13 - Pulsar
13 - Pulsar

Hi again @janani_venkat 

 

I think I understand, and you were on the right track with your workflow. Please give this a try and see if it does the trick

 

Capture.PNG

 

The Generate Records tool creates a record for each day of the current month.

The Filter tool keeps only weekdays (exclude Saturdays and Sundays)

The Join tool matches all weekday files in the directory.

The Sample tool keeps the most recent weekday and passes the full path to the Dynamic Input tool.

janani_venkat
7 - Meteor

Hi @ddiesel!

 

Thanks for the quick responses. Apologies - like many others I'm quite new to Alteryx.

 

When I ran your workflow, it seemed to pick a pdf file in the path. Would you mind appending this to the original workflow I sent? This is because my input path has other dynamic components/moving pieces as well (like the year and month) and this would need to get added on to that - does that make sense?

ddiesel
13 - Pulsar
13 - Pulsar

No worries! This is how I learned too - by asking questions here in the community.

 

We can add a filter after the directory tool to control the file types that are passed through.

Formula: CONTAINS([FullPath],".xlsx")

 

To make it dynamic to the current month and year, we can also filter for month and year in the FullPath/FileName. Alternatively, we can use any of the datetime stamps in the directory like CreationTime.

 

I see that year is part of the FullPath. Which part of the FullPath/FileName indicates the month of the file?

janani_venkat
7 - Meteor

Hi @ddiesel 

 

Just realized that my reply didn't go through! 

 

My folder path contains YYYY/MMYYYY/Data Dump/Data dump WD xx.xslx - I managed to add the YYYY/MMYYYY in my dynamic input. It's only the business day that I am struggling with

danilang
19 - Altair
19 - Altair

Hi @janani_venkat 

 

Any discussion of business days needs to consider holidays.  For instance, in Canada, Feb 15 is Family Day and must be removed from the list of business days.  The easiest way to do that have a list of holidays and join this to the generated list of days in the month, taking the L output.  Starting from @ddiesel's excellent solution, here's a modification that takes holidays into account

w.png 

Holiday's come in 2 types, static i.e. New Year's Day which is always Jan-01, and dynamic ones, like Family Day which is the 3rd Monday in February.  The static ones can be handled easily with a text box that holds the month and day for the holiday, "-01-01" and a Formula that prepends the current year. 

 

The dynamic ones are trickier and need to be handled on a case by case basis.  The 3rd Monday of any month will always be between 15th and the 21st, so generate all these dates and take the one that is a Monday.  Union this with the static holiday list and join to the list of dates generated for the current month.  The join is used as a filter to exclude the holidays, so use the L to get the days that don't match.

 

Dan

janani_venkat
7 - Meteor

Hi @danilang!

 

Thank you so much - I will have a look and come back.

 

Agree with you on the business day - I tried something akin to that to exclude holidays and weekends but was confused on how to achieve it in the dynamic path!

 

ddiesel
13 - Pulsar
13 - Pulsar

Great point, @danilang!

 

@janani_venkat, let us know if this works for you.

Labels