Dual returning the Numeric Value when using MIN expression

[#1]

I used the dual function to apply numeric value to my ratings, but when I use the MAX function in an expression, I'm getting the numeric value and not the text.

FYI: My script looked like:

if(RATING='big',dual(RATING,10),
if(RATING='med',dual(RATING,5),
if(RATING='small',dual(RATING,1),
dual(RATING,0)))) as DualRating

Thanks in advance.

[johnw · Accepted solution]

Max is a numeric function, so it returns a number. It's not smart enough to return a dual(), unfortunately. If you want to convert that back to a rating, you'd have to do it manually. However, since your rating texts are the opposite sequence of their associated numbers, I suspect this would work, at least if all you need is the text value.

minstring(RATING)

[Anonymous]

I expect that text(max(DualRating)) returns text value. Doesn't it?

[johnw]

I should check it rather than guessing, but my guess is "no". My guess is that QlikView will apply these functions simply and in order. First, it will use the max() function, which returns a simple number, like 10, not a dual(). Then it will give you the text() for 10, which is simply '10', and not a dual(). I'd be shocked and impressed if QlikView was smart enough to return 'big'.

[Anonymous]

John,
You're right, I just checked. The only way is manual, maybe something like if:
text(if(max(RATING)=10, 'big',
if(max(RATING)=5,, 'med,...

[#6]

What if I had an expression like this:

Max(Aggr(Sum(Amount) / Sum(Amount),Organization,Month) )

How would I output the correspinding Organization for the Max value ?