Modify field value in script

[Anonymous]

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.

[swuehl]

Try

LOAD

...

Replace(YOURFIELD,'SMX','77') as YOURFIELD

...

from YOURTABLE;

[johnw · Accepted solution]

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;

[Anonymous]

BINGO Mr. Witherspoon, spot on on your second suggestion.  (Yes, specific to left 3.)

Thank you very much!

Jonathan

[ngrunoz]

Hi John Is it possible to also use this with Wildcards?

[johnw]

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:

https://github.com/RobWunderlich/Qlikview-Components