Using batch macro to feed data into excel, and get the outputs from calculation of excel

[JYLim]

Hi community,

Currently, I have used Alteryx for calculation of 2million data and I need to pass it back to excel for calculation. Due to extensive formulas in excel and it will consume a lot of time in coding in the formula in Alteryx, I am currently using 1000 data as trial and feed it back to excel and let Excel do the calculation. However, 1000 data is too big for excel and it took 1 hour for the whole calculation to take place, therefore I am using tile function in equal recodes with 10 number of tiles, meaning that my data will only be 100 each block to feed into excel. My goal in this progress is to feed 1-100 data in excel, let excel populate the calculation and use Alteryx to extract the whole dataset completed with excel calculation and append them in one yxdb file. I am currently also trying to use a batch macro to loop the data from 1-100, 101-200, 201-300 etc but there was no luck, attach below is the batch macro I designed. I would like to consult the community on how my batch macro can be improved and is it possible that my dataset will autoprompt the calculation in excel for data in 101-200 and do all calculation in Alteryx withought needing to manually trigger the calculation in Excel?

[OTrieger · Accepted solution]

@JYLim 
2 million it is a huge data set, no matter what you will do it will take time!

Nevertheless in regards tp the batch macro, Not sure if you need the tile tool. If you will add a RecordID tool and then with a formula tool do a new field that equals RecordID/100, and assign to in a 64 Integer, you will get the needed categories. Change the category field to a String data type. Connect these output to the Macro Input and to a Unique tool bases on these categories and connect it to the Control Parameter.

In your batch macro connect the Control Parameer to the Filter tool Q to Q, not need Action tool.
Set the Filter tool [1]=catergory  field.

Now your batch macro will do the same over and over for each of one your catergories.