topic SV:How do I apply the DUAL function either in a script or in an expression to give number value to text fields? in QlikView

How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Tue, 03 Aug 2010 14:40:54 GMT

I have a field with ratings 'big,med,small'. I'd like to convert them to numbers without having to use a mapping table. Can someone show me the syntax using DUAL or other function?

Thanks in advance!

Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

martin59 — Tue, 03 Aug 2010 14:53:35 GMT

Hi,

You can use the match() or mixmatch() functions :

match(rate,'small','med','big')

SV:How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Tue, 03 Aug 2010 16:34:39 GMT

..or use a classic if:


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

This simple code makes a rating big get the dual value 10, med get 5, small get 1, and if anything else, it gets 0.

Cheers!

(closing parenthesis now also in code, thanks Scott ;P)

SV:How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

smoon63 — Tue, 03 Aug 2010 18:52:15 GMT

Just don't forget to close all those parentheses!

Scott

SV:How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Anonymous — Tue, 03 Aug 2010 20:06:54 GMT

Mapping can be used too:


RatingMap:
MAPPING LOAD * INLINE [
 A, B
 small, 1
 med, 5
 big, 10];
LOAD
 ...
 dual(Rating, applymap('RatingMap',Rating,0)) as DualRating
 ...

Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

johnw — Tue, 03 Aug 2010 20:16:49 GMT

Just to expand a bit on Martin's solution, which is probably what I'd personally use for a list this small, the dual() itself would then be this:

dual(rating,match(rating,'small','med','big'))

which assigns a value of 1 to small, 2 to med, and 3 to big. You could use that directly in a chart as written, but it would probably be better to put it in your script:

dual(rating,match(rating,'small','med','big')) as rating

I'd use Jakob's nested-IF solution if I indeed needed numbers like 10, 5 and 1 instead of 3, 2 and 1, at least for this short of a list.

I'd probably use Michael's mapping solution if I had a longer list. Not sure how much longer. Maybe 10 items or more?

Re: Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Thu, 16 Aug 2012 18:19:45 GMT

Hi John,

How would you use this to ensure date fields are sorted in Date order, not in alpha

i.e. Month name Jan, Feb, Mar, Apr etc....

How can we ensure that these will sort based on Month Number. Is it as simple as loading the dates in ascending order and using sort on LOAD ORDER or will the dual command work better for this using a mappingload of 1 thru 12 and the dual command?

Re: Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Thu, 16 Aug 2012 18:20:00 GMT

Hi John,

How would you use this to ensure date fields are sorted in Date order, not in alpha

i.e. Month name Jan, Feb, Mar, Apr etc....

Re: How do I apply the DUAL function either in a script or in an expression to give number value to text fields?

Thu, 16 Aug 2012 18:20:08 GMT

Hi John,

How would you use this to ensure date fields are sorted in Date order, not in alpha

i.e. Month name Jan, Feb, Mar, Apr etc....