Loop using a date range

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,

Tips and Tricks

Amazon S3

Iterative Macro

Accepted answers

mceleavey

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:

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

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

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.

Date Range.yxzp

Data Extract Macro.yxmc

All comments

shreyanshrathod

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

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.

tally

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,

dummy_data_20200101.csv

Data Extract Macro.yxmc

tally

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,

tally

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,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703