Pulling Previous Business Date input file from a folder

[meilingg]

Hi all,

I need help with Alteryx picking up the correct input file with filename that contains "Business Date -1" from a folder.

I have to utilize the data from previous business day “E” file only.

For example on 18th July 2025 (Friday), I will need to pull out data in “CLIENTAAA_20250717_E_TRADEFILE“ and on 21st July 2025 (Monday), I will need to pull out data in “CLIENTAAA_20250718_E_TRADEFILE” file.

I’ve tried using below combination and it didn’t work when I tried running it on Monday, it picked up the incorrect 9th July 2025 file (CLIENTAAA_20250709_E_TRADEFILE) due to last modified date timestamp.

I've attached my alteryx workflow = [1] Directory *CLIENTAAA**E_TRADEFILE* + [2] Sort LastWriteTime Descending + [3]  Sample First N rows + [4] Dynamic Input

Is there any way i can amend my Alteryx to grab the file (i.e.CLIENTAAA_(BUSINESS DATE RUNTIME - 1)_E_TRADEFILE) i desired?

Thank you so much in advance!

[Pilsner · Accepted solution]

Hello @meilingg 

If you are trying to select the file with the latest date in the file name, I would first recommend extracting the date out of the filename. You can do this as follows:

1) Use the regex tool to extract the date. \d{8} tells the regex tool to extract out 8 consecutive numbers (which appears to be your date format). If it cannot find 8 consecutive numbers, it will return null. 

2) Convert these 8 consecutive numbers to dates using the date time tool. As you have a 4-digit year (yyyy) followed by 2-digit month (MM) and 2-digit day (dd) we can select the "yyyyMMdd" option. 
 

 

 

3) Sort based on the new date field.
 

4) Sample the top one record.

  

In my version I have just used a text input with some dummy data but this should still work if you attach it to your directory tool, you might just need to change which column the tools point at. I've attached the workflow below.

I hope this helps and if you have any questions, please feel free to ask.

Regards - Pilsner

[meilingg · Accepted solution]

Awesome! Thank you for the detailed steps, Pilsner!
You are a life saver! 👏👍

[Pilsner]

That's great to hear, thank you. Glad I could help!