How to word an Expression

[Adam-Adam]

Hello All,

I was hoping you could help me with a query.

If I have a set of games and corresponding names and amount of money own.

I would like to formulate an expression whereby I can add the persons rank.

This is based on the assumption that the person who has won the highest money is in 1st position and so on and so on.

Here is what I have so far, this seems to be incorrect:

IF [Person_id] = [Row-1:Person_id] && [winner_flag] = "Y" THEN "FIRST"

ELSEIF [Row-2:Person_id] THEN "SECOND"

ELSEIF [Row-3:Person_id] THEN "THIRD"

ELSEIF [Row-4:Person_id] THEN "FOURTH"

ELSEIF [Row-5:Person_id] THEN "FIFTH"

ELSEIF [Row-6:Person_id] THEN "SIXTH"

ELSEIF [Row-7:Person_id] THEN "SEVENTH"

ELSEIF [Row-8:Person_id] THEN "EIGHTH"

ELSEIF [Row-9:Person_id] THEN "NINTH"

ELSEIF [Row-10:Person_id] THEN "TENTH"

ELSE "Other" ENDIF

Does anyone know which edits I need to make to the above expression so that it functions?

Thank you all in advance

[Luke_C]

Hi @Adam-Adam 

Can you provide some sample data? I would probably do something like:

1. Sort by Game + Money Won
2. Tile tool to assign ranks for each game
3. Create a static text input mapping 1 to First, 2 to Second, etc
4. Find and replace or Join to add the values (anything after 10 can be hardcoded to other)

[Christina_H]

Assuming the formula would do what you want it to if formatted correctly, you need to edit your ELSEIF statements.  It should be

ELSEIF [Person_id]=[Row-2:Person_id] THEN "SECOND"

However there might be more efficient ways to achieve what you're trying to do if you can provide sample data.

[Adam-Adam]

Thank you both for your response@christina_Hurell

Sample Data

Game:              Winner Flag     Person ID         Money Won

Darts                        Y                    Adam                £10

                                N                   John                    £5

                                N                     Harry                 £1

Pool                         Y                   John                  £8

                                N                   Adam                 £1

                                N                   Harry                 £0

Snooker                  Y                    Harry               £5

                                N                   John                 £3

                                N                   Adam                £1

Now obviously the data set is much bigger than this so I want to be able to have a column in Excel where I can say that per game, this person came 1st, 2nd, 3rd, 4th, 5th etc. up to 10th position.

Does that make sense? 

[Luke_C · Accepted solution]

Hi @Adam-Adam 

Here is an example of what I described above:

[phottovy · Accepted solution]

Here is a slightly modified version of @Luke_C example. The only real difference is I used the Multi-Row tool to rank by game instead of the Tile tool. The key with both examples is to use the "group by" option when calculating the rank. This allows the rankings to be recalculated for each game.

[Adam-Adam]

Huge thank you to both of you for helping answer my 1st community.alteryx question in such a short space of time.

As I was already using the Multi-Row tool, I have used that.

Thank you All!

[atcodedog05]

Hi @Adam-Adam 

Congrats on your first question 😀. Community is a really friendly space feel free to explore and seek guidance.

Cheers and have a nice day!