topic Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder in Alteryx Designer Desktop Discussions

Select 2 most recent files for ID in a folder and then copy the files to a new folder

guido_vianson — Mon, 29 Jan 2024 11:30:12 GMT

Hello,

i need to collect the files that I am receiving in a folder called 'Replies'. I have 3 type of possible files:

AIFFBR

AIFFDB

AIFFBH

These are submission receipts. This means that I am always getting AIFFBR% file, same for AIFFDB%. Then, if AIFFDB% is fine, I am also getting a AIFFBH% as confirmation.

They are .zip and .txt extension with same naming convention. I only need the .zip files and not the .txt.

I seek for a specific file by filtering on the ID contained in the File Name and on the Creation Time column.

AIFFDB-100000000-A00001600-V00002700-00000000-AIF-20240126165425_20240129113335675.zip

AIFFBH-100000000-A00002000-V00006400-00000000-AIF-20240127042128_20240129103427384.zip

This is the file naming convention.

Most recent is based on the column of the folder 'Creation Time'

If I am filtering for V000001-0000000 and a specific date range I might get multiple files.

This depends on how many times the submission was done.

I only want to select the most recent AIFFDB and AIFFBH files for each ID I am looking for.

The result should then be copied to a new folder in the download folder.

can you please help me to build the same?

So far I am importing to Alteryx using a Directory.

Then I am filtering using 'contains' in File Name but I need to find a better way as I might need to search 40 different ID.

The challenge is how to select the 2 most recent file for ID and then copy the same to a new folder?

I tried to understand how to use a CMD but not very clear for me.

Thank for your help

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

ChrisTX — Mon, 29 Jan 2024 11:26:48 GMT

Can you post a sample of actual file names in table format or text format in this forum?

When you said "I only want to select the most recent AIFFDB and AIFFBH files"

Is "the most recent" determined by file creation date?

Chris

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

guido_vianson — Mon, 29 Jan 2024 11:29:47 GMT

AIFFDB-100000000-A00001600-V00002700-00000000-AIF-20240126165425_20240129113335675.zip

AIFFBH-100000000-A00002000-V00006400-00000000-AIF-20240127042128_20240129103427384.zip

This is the file naming convention.

Most recent is based on the column of the folder 'Creation Time'

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

alexnajm — Mon, 29 Jan 2024 11:31:40 GMT

If you have the Directory tool already, it’ll be a matter of parsing out the relevant information from the file name to give yourself a column with each “group”, sorting the data on Creation Date like @ChrisTX is mentioning, and Sampling the First N Records with N=1 and grouping by that group!

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

ChrisTX — Mon, 29 Jan 2024 11:59:21 GMT

See if the attached workflow points you in the right direction

Chris

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

alexnajm — Mon, 29 Jan 2024 12:00:01 GMT

Nice @ChrisTX !

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

guido_vianson — Mon, 29 Jan 2024 14:33:01 GMT

@ChrisTX yes it helped thanks!!. I changed some fields and added a formula to concatenate 2 fields.

Do you also know how to copy the result in a new folder?

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

ChrisTX — Mon, 29 Jan 2024 17:02:08 GMT

I don't understand the question "how to copy the result in a new folder"

Do you want to create a new folder then save an output file in that folder?

What's an example format for the new folder name.

To create a folder: you will likely need to create and run a BAT file using the Run Command tool under the Developer category.

Chris

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

guido_vianson — Tue, 30 Jan 2024 10:44:30 GMT

Yes exactly. I would like the files to be copied to a a new folder (no name convention needed) in the Downloads folder

Re: Select 2 most recent files for ID in a folder and then copy the files to a new folder

ChrisTX — Tue, 30 Jan 2024 11:29:46 GMT

Try this post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-copy-a-file-from-one-folder-to-another/td-p/91171