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

[#1]

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!

[martin59 · Accepted solution]

Hi,

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

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

[#3]

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

[smoon63]

Just don't forget to close all those parentheses!

Scott

[Anonymous]

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

[johnw]

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?

[#7]

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?

[#8]

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?

[#9]

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?