Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Loop using a date range

tally
7 - Meteor

Hi all,

 

I have created a macro which takes the following input:

date

 

The date is used to find the file name: data_source_{date}.csv

This file is loaded and then uploaded to S3.

 

I would like to create something that would allow me to loop through dates using a start date and end date to run the macro. Each individual day has it's own csv file, which is the reason I have to create this loop.

 

In addition to this, it would be great to have the macro keep on running if it cannot find a file, so if it fails for one date, skip it and attempt the next date.

 

Would this be possible?

 

Thanks,

T

 

8 REPLIES 8
shreyanshrathod
11 - Bolide

Hi @tally ,

 

There are a couple of ways to achieve this.

 

1) You can set up two Date tools from Interface Tool palette and use them to control the files that you wish to read.

 

2) You can manually enter start_date and end_date in two separate columns, use Generate rows tool to generate dates and then use this dates as a control parameter input to your Batch Macro.

 

The major question here is, how do you wish to process the data from different files? Is the structure similar of all files and you just want to UNION them or different files will serve as different inputs for further processing?

 

Let me know and I am sure we can build something to solve your query.

 

Regards,

Shreyansh Rathod

mceleavey
16 - Nebula

Hi @tally ,

 

the best way to achieve this is to have an input tool set in a macro, with the grouped dates fed into the macro as a control parameter. This control parameter will overwrite the part of the filename in the input tool which represents the date.

This way, the whole thing will loop through every date.

 

M.

tally
7 - Meteor

Hi @shreyanshrathod.,

 

Thanks for the quick response. I've uploaded the workflow with some dummy data. Please note, I've removed my credentials from the S3 tool - however I have kept it there.

If you look at the formula, the file output requires the date as a parameter so that it saves the output to the correct bucket location as the table will use the date as a partition: s3://bucket/table_name/date={date}

 

There is no data transformation required, this job is being done to transfer data from one location to another using Alteryx as the automation tool.

 

I'm not so sure how I could implement the suggestions that you have provided, could you please adivse?

 

Thanks,

T

 

tally
7 - Meteor

Hi @mceleavey,

 

Would it be possible to see a use-case example so that I could replicate this?

I have uploaded some dummy data along with the macro in the previous reply if this will aid.

 

Thanks,

mceleavey
16 - Nebula

Hi Tally,

 

I think this is what you want, but there are other ways of doing this.

I've built this as an app, so you pick the date range:

 

mceleavey_0-1620730950717.png

This then generates the dates in between the start and end dates and feeds these dates in to the macro as a control parameter:

 

mceleavey_1-1620730991117.png

 

This then simply updates the date in the filename string in the Action tool:

 

mceleavey_2-1620731021050.png

 

This will work providing all the files exist. If a file does not exist you will need to change the method as this will throw an error.

 

Another way of doing this is to use the directory tool to load in the names of all files in a given folder and sub-folders which have the same naming convention, then use the same method to filter the records according to the name of the file containing the dates you have selected. This would be my preferred methodology.

 

The workflow is attached, you will just need to change the filepath in the macro and ensure the action tool is set to overwrite the date part of the filepath you've selected.

 

M.

shreyanshrathod
11 - Bolide

Hi @tally ,

 

You can build your solution upon this.

Note I have divided the entire process into two separate macros for convenience, but you can integrate into a single macro as well.

The first macro asks  the user to specify the folder path where csv files are located, it asks user to select two dates (start and end) and then it filters out files between those two dates.

 

This then works as an input to the second macro which actually reads data from this files and loads them into output. As an example, I have used a standard output tool to write csv files,  but you can connect your S3 over here. 

 

You can derive the filenames from the workflow and use them to load different csv's into your S3 bucket.

 

Regards,

Shreyansh Rathod

tally
7 - Meteor

Hi @mceleavey,

 

Thank you for your answer! Modifying the batch macro slightly has allowed me to create the app that is fits my requirements.

 

Thanks,

mceleavey
16 - Nebula

no problem.gif

Labels