Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field I am pulling in that is sometimes prefaced with "SMX", like "SMX0012345" Often times it is just "0012345"
What I would like to do in the script is IF LEFT 3 LIKE 'SMX', REPLACE 'SMX' with '77'
So SMX0012345 becomes 770012345, while 0012345 remains 0012345.
Thanks in advance.
JL.
And if you have more than a few such replacements, I like to handle it with a mapping table rather than nesting replaces:
[Text Substitutions]:
MAPPING LOAD * INLINE [
From, To
SMX, 77
ABC, XYZ
Hello, Goodbye
];
LOAD
...
,mapsubstring('Text Substitutions',YOURFIELD) as YOURFIELD
...
from YOURTABLE;
Neither of our solutions is specifically checking that it's the left three bytes, though. If that's important:
LOAD
...
,if(left(YOURFIELD,3)='SMX','77'&mid(YOURFIELD,4),YOURFIELD) as YOURFIELD
...
from YOURTABLE;
Try
LOAD
...
Replace(YOURFIELD,'SMX','77') as YOURFIELD
...
from YOURTABLE;
And if you have more than a few such replacements, I like to handle it with a mapping table rather than nesting replaces:
[Text Substitutions]:
MAPPING LOAD * INLINE [
From, To
SMX, 77
ABC, XYZ
Hello, Goodbye
];
LOAD
...
,mapsubstring('Text Substitutions',YOURFIELD) as YOURFIELD
...
from YOURTABLE;
Neither of our solutions is specifically checking that it's the left three bytes, though. If that's important:
LOAD
...
,if(left(YOURFIELD,3)='SMX','77'&mid(YOURFIELD,4),YOURFIELD) as YOURFIELD
...
from YOURTABLE;
BINGO Mr. Witherspoon, spot on on your second suggestion. (Yes, specific to left 3.)
Thank you very much!
Jonathan
Hi John Is it possible to also use this with Wildcards?
Not mapsubstring(), but you could do a pick(1+wildmatch(field,wild1,wild2,wild3),field,new1,new2,new3). A way to set something like this up with a table, just like you might use with mapsubstring, is given by rwunderlich in his QlikView cookbook:
http://qlikviewcookbook.com/download/mapping-with-wildcards/
It looks like there's a version of this in QlikView Components as well: