How to union all the last file by date and charge it in a table

[Christopher_ZK]

Hello Everyone ,

I have a beginner level in alteryx designer .
I have many xlsx files in a repository :

South_01.01.2021.xlsx 

South_03.01.2021.xlsx 

South_04.01.2021.xlsx 

South_06.01.2021.xlsx 

South_02.02.2021.xlsx 

South_05.02.2021.xlsx 

South_06.02.2021.xlsx 

South_02.03.2021.xlsx 

South_05.03.2021.xlsx 

South_07.03.2021.xlsx 

I want to make a workflow that take the latest file of each month and union all, in this case :

South_06.01.2021.xlsx

South_06.02.2021.xlsx 

South_07.03.2021.xlsx 


The union result will be charge in a table 

Can you help me please 
Kind Regards

[Christina_H · Accepted solution]

Use this formula to extract the date from the file names and convert it to the first of the month:

DateTimeTrim(DateTimeParse(Right([Filename],15),"%d.%m.%y"),"month")

Then use a sample tool to take the last row for each month.

Wrapped around this, you probably need a directory tool to get your list of filenames, and a dynamic input or macro to extract the data from them.

[ShankerV · Accepted solution]

Hi @Christopher_ZK 

Can explain you step by step to achieve the output.

Step 1: Use directory tool to read all the file names from the directory.

Step 2: Use Regex tool

(\d{2}.{8})

Step 3: Formula tool

DateTimeParse([RegExOut1],"%d.%m.%y")

DateTimeDay([RegExOut1])

DateTimeMonth([RegExOut1])

Step 4: Sort tool

Step 5: Sample tool

Post run: You will get the latest filenames for every month.

Many thanks

Shanker V

[Christopher_ZK]

THANKS, IT WORKS