Batch macro on Summarize

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?

Developer Tools

Custom Tools

Macros

Batch Macro

Iterative Macro

Accepted answers

danilang

Hi @chriscgmedia

@GiuseppeC had good attempt at a solution but I don't think he was aware of the disappearing column issue that you're facing. His macro requires that all the columns be mapped to an input, but sometimes the columns aren't there, so the macro won't launch. To get around this issue I think you can modify your summarizing strategy.

When you have a situation like you have in your Summarize(20)(in red), 1 Group by with multiple sums on fields which may or may not be there, you can replace it with a transpose/summarize/cross tab(in green) to remove the multiple sums.

In the transpose, use everything except the summed fields as keys and include the summed fields and Dynamic as data columns. Your summarize now becomes

with only Insertion Order changing on every iteration. "Name" is always in the grouping and the sum is always on "value". After a formula to include Sum_ in the field name and a subsequent transpose, you end up with

which matches the output of Summarize(20) aside from the column order and the extra "_"s in the column names. You can add or remove columns that you're summing and the workflow will adjust. You may want to transpose the data before input to the macro to avoid having to transpose multiple times.

Note I replaced the .csv template in your macro input with a .yxdb since it loads so much faster.

Dan

Batch_FilterFields_Correlate.yxmc

outputmail.yxdb

All comments

GiuseppeC

Hi @chriscgmedia,

I have mocked up a solution for you (attached) using a batch macro approach and the 'Update Raw XML with Formula' option in the Action tool.

(*The attached files have been generated with Designer 2019.3, if you are on an earlier version, open them with Notepad and change 2019.3 with your own version*)

Not sure how you collect the user selection, so I made a few assumptions here (see Text Input below), but hopefully you can take this as a reference and apply it to your own data.

This is your sample data that different users want to summarize by different metrics (A, B and C):

This is your User 'selection' table. In this case User 'a' wants to Sum by metric A and B, user 'b' by B and C, user 'b' by C only:

The Macro has as many control parameters as the number of metrics in your data. (There are definitely smarter solutions here, but hopefully this simplify the concept of updating the raw XML dynamically).

I have also added another control parameter to be able to pass through the user who is doing the selection in the ouput table.

This his how the output looks like:

Hope this helps!

Giuseppe

useDynamicSummarize.yxmd

dynamicSummarize.yxmc

danilang

hi @chriscgmedia

Thanks for the mention.

If @GiuseppeC's excellent solution doesn't solve your problem can you post some sample data and what you'd like your output to look like?

Dan

chriscgmedia

My workflow is attached.

I am having issues with the Summarize (20) tool in the Batch_FilterFields_Correlate macro.

I don't actually have a record of users and what they want to add, I have dynamically selected the list of numeric and string fields so that I have a record of metrics and dimensions.

Your update value with XML looks like the way forwards!

Is it possible to update the XML dynamically?

I have the list of dimensions which I can use in the batch macro, so this would just be a case of updating the XML, can this run together with a batch macro?

TheMAP_Dynamic_V2.yxmd

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 )

sampledatafoodsales.xlsx

Calculation table.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703