Automating Weekly Folder Detection in the Directory Tool

[KamenRider]

I'm looking to automate the process of updating the folder name in the Directory tool each week. Currently, for a folder named "Week of 0103," it contains files like DailyActivityReport 01-02-2025.xlsx, DailyActivityReport 01-03-2025.xlsx, DailyActivityReport 12-30-2024.xlsx, and DailyActivityReport 12-31-2024.xlsx. In the subsequent weeks, new folders will be created, such as "Week of 0110," "Week of 0117," and so on. I want the Directory tool to automatically detect and read the correct folder each week. There should be a tool or formula that can adjust and read the folder name dynamically on a weekly basis.

Looking forward to hear from anyone.

Kamen

[BS_THE_ANALYST · Accepted solution]

Hey @KamenRider, cool problem. 

Given that you have "Week of 0103", I'm assuming for whatever reason this is 2025 we're looking at and 0103 represents the first Friday of January i.e. 2025-01-03. So I'll present this solution on that basis.

Let's start by identifying which "Week of XXYY" we're currently in (I used chatGPT for the expression!). Check the results window, it seems to have detected the current week associated to the following Friday. (see explanation at the end of the post for ChatGPT's breakdown)

 

 Now let's take that value from the results window, prepend it with a folder where all these "Week of " folders live (obviously, your path is different to mine)

Now we'll take that path as it's obviously dynamic as the date was calculated using DateTimeToday. We just need this value to slip into the Directory Tool's folder that it's searching. Therefore, let's use a batch macro with the following configuration:

 

The control parameter will take that value and update the directory tool.

I've attached the workflow for you to play with. I did generate the formula's expression with chatGPT so take that how you wish. The only piece I think you may need to adjust is the formula.

Lastly, ChatGPT's explanation of the formula:

Objective:

The goal of this formula is to dynamically calculate the date of the upcoming Friday and format it as "Week of MMDD". This is useful when you need to create folder names or labels that reflect the current week, based on Friday's date, which is common in business scenarios that track weekly data with Friday as the reference point.

---

Step-by-Step Explanation

Now, following the objective, here's the formula breakdown:

1. Get today's date → DateTimeToday(): This gives us the current date.
2. Calculate days to next Friday:
   • If today is Friday (%w = 5), we stay on today (add 0 days).
   • If today is Saturday (%w = 6), we add 6 days to get to the next Friday.
   • If today is Sunday (%w = 0), we add 5 days to get to the next Friday.
   • If today is Monday, Tuesday, Wednesday, or Thursday (%w = 1-4), we calculate 5 - %w to get to Friday (e.g., for Monday, we add 4 days).
3. Add the calculated days to today's date → DateTimeAdd(DateTimeToday(), ..., "days"): This gives us the exact upcoming Friday.
4. Format the result as "MMDD" → DateTimeFormat(..., "%m%d"): This takes the date of the Friday and formats it as 0105 for January 5th.
5. Prefix with "Week of " → 'Week of ' + result: This combines everything to give a readable label like "Week of 0105".

[KamenRider]

Hi @BS_THE_ANALYST 

Can you please somewhat help me adjust the formula. The result is giving me 0214 instead of 0207. The system can't find the specified path.

Thanks,

Kamen

[alexnajm]

@KamenRider I think you can adjust the formulas from here to make it work for what you need! @BS_THE_ANALYST did a brilliant job creating a solution that seems to work well - the last part explain how it calculates the date so I would adjust to your needs. 

[KamenRider]

Hi @BS_THE_ANALYST 

Sorry for a delay reply. It takes me time to understand how I am going to incorporate it in the workflow I have. Made some changes in your formula to get the last Friday date which for example 0207

 

Is it acceptable to copy and paste the Batch Macro, excluding folders such as _external? Will it function properly on the Alteryx server?

Please advised.

Kamen

[BS_THE_ANALYST]

Hi @KamenRider. Glad to hear you've tinkered with the formula and you've got it working for your use case.

"Is it acceptable to copy and paste the Batch Macro, excluding folders such as _external?"

Yes you should be able to move the batch macro wherever you like. My advice it to try it: locally, move it to a different location and see if you can run a workflow from it. The batch macro doesn't depend on any folders.

"Will it function properly on the Alteryx server?"

I don't see any issues with it being on Alteryx Server. If you do find any issues, please reach out and I'll advise as best I can.

[KamenRider]

Thanks @BS_THE_ANALYST  for the advise. In case I have any issues, surely will reach to you. 

Thanks for the assistance and patience.

Kamen