topic Dual returning the Numeric Value when using MIN expression in QlikView

Dual returning the Numeric Value when using MIN expression

Fri, 06 Aug 2010 15:52:18 GMT

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.

Dual returning the Numeric Value when using MIN expression

johnw — Fri, 06 Aug 2010 19:42:24 GMT

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)

Dual returning the Numeric Value when using MIN expression

Anonymous — Fri, 06 Aug 2010 21:31:49 GMT

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

Dual returning the Numeric Value when using MIN expression

johnw — Fri, 06 Aug 2010 21:54:44 GMT

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'.

Dual returning the Numeric Value when using MIN expression

Anonymous — Sat, 07 Aug 2010 01:02:52 GMT

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,...

Re: Dual returning the Numeric Value when using MIN expression

Thu, 06 Dec 2012 15:51:06 GMT

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 ?