Macro for creating multiple files

[saibal_78]

I have a dataset which has few columns like product, unit rate, Applicable Rate, Period Start and Period End as shown below.

Now I need to export data into multiple files like below and so on, this will be applicable for other year as well

Any help will be highly appreciated

Thanks

[JoeS]

Hi @saibal_78 

You shouldnt actually need to create a macro in order to do this.

You can use the "Output File name from field" option in the output data tool.

There is a guide here:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Dynamically-rename-output-fi...

Let me know if you have any further questions though.

[JosephSerpis]

Hi @saibal_78 you can create a batch macro to resolve this problem however there other ways which are simpler and don't require a batch macro. The attached workflow showcase 3 ways you can achieve this including a batch macro option.

[estherb47]

Hi @saibal_78 

The fastest way to do this might be with multiple Filters on the period columns. Filter for your first value, then another filter to check the next value, etc.

You could set up an iterative macro as well, that runs through each unique period, and then creates your new file. If you want to go that route, then in that macro, add a unique ID per period (I like the Tile tool for this process). You'd set up the filter to pull the record IDs with Iteration number +1 (Iteration # is zero based, if I'm recalling correctly), write those out, and then loop back with the F side of the filter. Next iteration would pull the next period, and so on.

For a macro, I'd go with Iterative over batch, but that's my inclination.

Edited: I completely forgot about the output options on the field!! Thanks, @JoeS . That's the easiest way here 🙂

Let me know if that helps!

Cheers,

Esther 

[saibal_78]

Hi JoeS,

May be I was not able to explain clearly, I have three columns which are very important here 1) Applicable Rate 2) Period Start and 3) Period End. Now I want macro to first set a filter and take a period for example "2015 Rate" and then start creating multiple files based on "Period Start" and "Period End" as mentioned below.

For Applicable Rate = "2015 Rate"    start creating output files as "Unit Rate 2015_Period 201501", "Unit Rate 2015_Period 201502", "Unit Rate 2015_Period 201503" and so on till Period End value i.e. "Unit Rate 2015_Period 201512"

Same process would take other period as well and continue creating individual output files till the Period End.

Thanks

[estherb47 · Accepted solution]

Hi @saibal_78 

If I read your last comment correctly, you need to generate all of the in between periods, from Start Period to End Period.

You can do this with the Generate Rows tool, no macro needed.

For each row in your original data, a new field called [Period] will start at the value of [Period Start]

If Period is <= Period End, Alteryx will add another row to the original data, and increase [Period] by 1.

You can then use the Output Data tool, and the new [Period] field, to create the files.

Let me know if that helps.

Cheers!

Esther

[JoeS · Accepted solution]

---

@saibal_78 wrote:

Hi JoeS,

May be I was not able to explain clearly, I have three columns which are very important here 1) Applicable Rate 2) Period Start and 3) Period End. Now I want macro to first set a filter and take a period for example "2015 Rate" and then start creating multiple files based on "Period Start" and "Period End" as mentioned below.

For Applicable Rate = "2015 Rate"    start creating output files as "Unit Rate 2015_Period 201501", "Unit Rate 2015_Period 201502", "Unit Rate 2015_Period 201503" and so on till Period End value i.e. "Unit Rate 2015_Period 201512"

Same process would take other period as well and continue creating individual output files till the Period End.

Thanks

---

Hi @saibal_78 

You should be able to do this using those options still. You'll need to create the name of the files you want as a field in your data. Once you have done that you can then use that for the option in the output tool.

Are you able to upload some sample data for me to build an example of what I mean?

Thanks

[saibal_78]

Awesome solution ..........everything is working perfect using both of yours solution...thanks a ton

[saibal_78]

Thanks JoeS for the solution

[JoeS]

---

@saibal_78 wrote:

Thanks JoeS for the solution

---

Great stuff, glad you got it working how you needed!