Ranking columns per row (by field values)

[Denikin]

Let's assume I have a dataset in following structure:

ID	Event1_probability	Event2_probability	Event3_probability	Event4_probability
1	0.1	0.05	0.12	0.15
2	0.21	0.03	0.11	0.17

I want to add information about which event is:

- most probable

- 2nd most probable

etc.

Per each row.

Therefore, I would like to achieve the following structure:

ID	Event1_probability	Event2_probability	Event3_probability	Event4_probability	1st_probability_event	2nd_probability_event	3rd_probability_event	4th_probability_event
1	0.1	0.05	0.12	0.15	Event4	Event3	Event1	Event2
2	0.21	0.03	0.11	0.17	Event1	Event4	Event3	Event2

How to achieve it without writing enourmous IF statements for each new column?

Thanks in advance for any help!

[derekbelyea · Accepted solution]

This should do it.

[Denikin]

Thanks, works great!

[cliffjordan]

Denikin,

I have often run into the same scenario, so I created a Rank Macro called RankMacro.yxmc that you can use. You use it like any other Preparation Tool and just choose which fields you want to create ranks for. 

Here are the details:

"RankMacro is a standard macro that will rank fields within a record. The user chooses the fields he wants to Rank and the direction of the Rank (Descending or Ascending). The macro creates an additional field for each of the chosen rank fields to store the Rank value. These new fields will have the same name with a "_Rank" suffix."

You can download this from the Gallery. Here is the link: 

https://gallery.alteryx.com/#!app/RankMacro/5e12839c8a933710f08216c2

Enjoy!

-cliff