
Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?
Martin FAVIER Aug 3, 2010 10:53 AM (in response to Henry Noh)Hi,
You can use the match() or mixmatch() functions :
match(rate,'small','med','big')

Re. :How do I apply the DUAL function either in a script or in an expression to give number value to text fields?
John Witherspoon Aug 3, 2010 4:16 PM (in response to Martin FAVIER)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 nestedIF 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?
debarrow Aug 16, 2012 2:19 PM (in response to John Witherspoon )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?
debarrow Aug 16, 2012 2:20 PM (in response to John Witherspoon )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?



SV:How do I apply the DUAL function either in a script or in an expression to give number value to text fields?
Jakob Berglund Aug 3, 2010 12:34 PM (in response to Henry Noh)..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?
Scott Moon Aug 3, 2010 2:52 PM (in response to Jakob Berglund)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?
Michael Solomovich Aug 3, 2010 4:06 PM (in response to Jakob Berglund)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?
debarrow Aug 16, 2012 2:20 PM (in response to Henry Noh)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?