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...
Thank you in advance.
Solved! Go to Solution.
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.
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:
and then join them to have this output (Var with an "a" are the ascending ones and with "d" descending).
Company ID | Sector | Var1_a | Var2_a | Var3_a | Var4_a | Var5_a | Var6_a | Var1_d | Var2_d | Var3_d | Var4_d |
1 | 1 | 1 | 2 | ... | |||||||
2 | 1 | 2 | 1 | ... | |||||||
3 | 1 | 3 | 3 | ... | |||||||
4 | 2 | 1 | 4 | ... | |||||||
5 | 2 | 2 | 3 | ... | |||||||
6 | 2 | 3 | 2 | ... | |||||||
7 | 2 | 4 | 1 | ... | |||||||
8 | 3 | 1 | 1 | ... | |||||||
9 | 3 | 2 | 2 | ... |
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.
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.
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.