Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Batch macro on Summarize

chriscgmedia
7 - Meteor

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? 

12 REPLIES 12
GiuseppeC
Alteryx
Alteryx

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):

image.png

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:

image.png

 

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.

image.png

 

This his how the output looks like:

image.png

Hope this helps!

 

Giuseppe

 

 

danilang
19 - Altair
19 - Altair

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
7 - Meteor

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?

danilang
19 - Altair
19 - Altair

Hi @chriscgmedia 

 

Your workflow references a macro called "Batch_FilterFields_Correlate.yxmc".  Can you provide this as well?

 

Dan

chriscgmedia
7 - Meteor

Attached are the three macros I use in this, the Batch one you very kindly helped me with! Then a standardize which I found on here, finally my own correlation macro which runs the different KPIs against the cost. 

GiuseppeC
Alteryx
Alteryx

Thanks @danilang!

 

@chriscgmedia can you post some sample data too? (The .yxmd format doesn't automatically include the data assets, you need to export the workflow into a .yxzp package if you want to attach input files too).

 

So, to clarify, you want the Summarize tool in the batch macro to Sum by the fields coming from the Find Dimensions container, right?

 

Cheers,

Giuseppe

chriscgmedia
7 - Meteor

Demo data is attached. 

 

Yes, I'm trying to get the workflow to detect the dimensions and metrics, then summarise by the grouped dimensions and sum by the metrics which I then build my KPIs from. 

danilang
19 - Altair
19 - Altair

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. 

 

w.png

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     

 

s.png 

 

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

 

r.png

 

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

 

 

chriscgmedia
7 - Meteor

That works! Thanks guys.

 

Is there a way to avoid field name truncation? 

 

My issue is now that I foolishly changed the names earlier on manually and they are pulling through dynamically ruining formulas!

 

Thanks all,

 

Chris

Labels