Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Build a batch macro that runs for each value in a field

Highlighted
7 - Meteor

Hi!

 

How can I build a batch macro that runs for each company sector? Basically, I have three fields as an input - a company ID, a sector ID where the company belongs and a numeric variable (financial ratio). The output should be a rank attributed to each variable (and the workflow for that is already done - attached). So what I need to know is what's missing on my workflow in order to have the values for all sectors... I've built a filter and using a control parameter to update the value in this filter... 

sarasustelosantos_0-1590790729804.png

Thank you in advance.

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @sarasustelosantos 

 

This batch macro now needs to be used in another workflow (which I've attached). I've set up the macro to run once for each Setor, hence the Summarize Tool grouping by Setor. See attached.

 

PhilipMannering_0-1590816128317.png

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I'm not sure exactly what your macro is doing, but there is a simpler way to create this rank by Setor by checking to Group By Setor in the Mult-Row Tool. See potential alternative solution attached.

 

PhilipMannering_0-1590816638429.png

 

 

Best,

P

 

Highlighted
7 - Meteor

Hi @PhilipMannering . Thank you so much for the solution to my problem! About the macro, it's a bit more complex that what I gave as an example. In reality my dataset is not just a company ID, a sector and one variable, but I have 10 variables for each company - 6 of them need to be ranked in an ascending way (just like it is is my workflow) and 4 of them need to be ranked descending. The rank workflow you suggest is not exactly what I want to do because I am doing a Rank Average (like the Rank.Avg function in excel). I was doing a batch macro because I need an ouput that gives me the rank per sector and the count of records for that sector. I have 72 sectors in my sample. So I was going to use this macro 6 times (for each ascending variable) and replicate the macro but descending for the 4 other variables like this:

sarasustelosantos_0-1590828139236.png

and then join them to have this output (Var with an "a" are the ascending ones and with "d" descending).

 

Company IDSectorVar1_aVar2_aVar3_aVar4_aVar5_aVar6_aVar1_dVar2_dVar3_dVar4_d
1112...       
2121...       
3133...       
4214...       
5223...       
6232...       
7241...       
8311...       
9322...       

Plus a Count column for each variable that gives the the number of companies (not null) that have that variable per sector (just like my workflow was doing by counting records per sector).

 

Do you think this is the best way to do this - by replicating this macro 10 times or is there a way to build a batch macro that groups by sector, and runs each time for one of those variables? Perhaps you have another suggestion to do this? Thank you so much for your help already 🙂

 

Attached is my potential macro workflow to perform all this at once.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hey @sarasustelosantos . No problem.

 

Ah, I see. Yes, doing a rank average does complicate things somewhat. You could use 10 macros to compute the rank average And if that works for you then great. I had a go at trying to build something that would work for multiple fields all in one macro. Hopefully I implemented correctly. See attached. 

Highlighted
7 - Meteor

Wow - I am so impressed with the solution you presented @PhilipMannering , very simple and effective. Thank you :)!!

 

Could we join this solution with the previous one so that we can also group by sector? Because each company is part of a sector and I want to do a rank for each of those variables within each sector.

 

So in the output we would have the ranks for each variable, the count of non nulls per variable and the sector which the company belongs. 

 

I've added two more things to your solution - a filter because I don't want to rank null values, those ranks should stay null, and a count in the end to know how many non null values I have for each variable. I also added in the input a sector column. 

Labels