Who’s on 1st

In QlikView/Sense one of the things that comes up periodically is rank. I have seen a number of posts where people have asked about appending a suffix to a number to indicate where you are positioned.

1st, 2nd, 3rd, 4th and so on. There are a couple of excellent posts on the community site giving a solution for a calendar, but what if you want more than 30? I embarked on creating a simple function that could be easily reused. I was also inspired by Steve Dark’s blog post about using formulas to create functions. If you missed it – you can find it here: Quick Intelligence Post.

This combines a couple of operations:

Match – for catching the special cases

Mod – to limit the number of cases

Pick – to do the selection.

You really only care about the last digit, so Pick is perfect for that. You just need to offset things by 1 in order to handle zero. Since this is a formula and you are going to pass in a value when you use it – $1 will represent the number.

Pick(Right($1,1), ‘th’, ‘st’, ‘nd’, ‘rd’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’)

The problem is the special cases. 1st is good, but 11st makes no sense. The exceptions are anything that ends in 11/12/13. This comes up in any multiple of 100, so the way I addressed that is to strip off anything over two digits using Mod() (Short for Modulus). This returns the remainder of dividing by the value provided, so 101 becomes 1, 2414 becomes 14.

This makes the final expression:

set vNumberSuffix = If(Match(Mod($1,100), 11, 12, 13), ‘th’, Pick(Num(Right($1,1), ‘#’)+1, ‘th’, ‘st’, ‘nd’, ‘rd’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’));

This expression is part of a load script, so I used SET to initialize the string but not evaluate it.

You can also set it up in the Variable editor if you are so inclined.

When you use this in an expression it becomes $(vNumberSuffix(numbervalue)), where numbervalue is your numeric field or formula.