Batch macro on Summarize

[chriscgmedia]

I have been learning about Batch Macros on community and thanks for your communal help (especially @danilang!).

I have an issue where I am trying to create a batch macro on a summarize tool.

My current workflow has a user inputting data, I then create lists of their dimensions and their metrics, these go into my summarize tool. 

The tool should 'Group by' by one dimension at a time and use their metrics as a 'Sum by'. 

So far I have set up my batch macro so it finds a list of dimensions and then goes through the summarise with one dimension at a time as a 'Group by', half way there!

What I can not figure out is how to create the dynamic 'Sum by' as it may be just one metric, it may be a number of them.

Is there a way to do this in batch macro or do I need another tool? 

[danilang]

Hi @chriscgmedia 

Sorry, I missed the truncation on the 1st run through.  Add a select tool and increase the Name field length to a large number.

 

Dan

[chriscgmedia]

Brilliant! Thanks, I had a few regex_replace tools which solved the issue but wasn't too smart!

[shwtswnt]

Hope you are doing well!

I have attached 2 files.
1. Raw data sheet name : Sampledatafoodsales.xlsx
2. Calculation Table.xlsx

Now here I am trying to take number of rows from 1st data sheet as per calculation table.

Steps following manually. Wanted to do it auto and dynamic. As calculation table numbers will change every week for sampling. I tried but unable to do this activity auto.
So my request is guide me for activity like I have sample data food sales file and I have to simply copy and paste number of rows to new file “Eligible data “ but according to calculation table.
First Highest Total Price entries and then Random Entries.

1. Filter on column Region then column Category.
2. Sort column Total price Highest to lowest amount.
3. Now here I am following calculation table to select sample as per table.
Will select Region – EAST & Category- Cookies and then as per table select Highest Total Price entry 1 then copy and paste into new excel file will give name to new file Name : eligible data.
Next is select Region – EAST & Category – Cookies and as per table now Random is 0 so will not take any random entry.

Next is select Region- West & Category – Snacks now as per table I have to take Highest Total Prices – 13 entries copy and paste into eligible data Sheet
Next is select Region – West & Category – Snacks as per table here will select Random column any 4 entries copy and paste into eligible data Sheet ( while selecting random do not consider those 13 entries which we have already copy and paste into new file it can be any 4 entries from sample file)

Last line select Region – now region is blank & Category is Cookies as per table Highest Total price – 5 entries copy and paste into eligible data sheet
Region – Blank & Category is Cookies As per table Random- any 6 entries select Copy and paste into eligible data sheet

( I have tried with Alteryx batch macro but not able to do also I have tried workflow and joint tool but still not able to pickup auto entries. )

(Also calculation table is dynamic and numbers keeps change )