topic Re: Modify field value in script in QlikView

Modify field value in script

Anonymous — Thu, 29 Dec 2011 22:53:27 GMT

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.

Modify field value in script

swuehl — Thu, 29 Dec 2011 23:02:09 GMT

Try

LOAD

...

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

...

from YOURTABLE;

Re: Modify field value in script

johnw — Thu, 29 Dec 2011 23:12:19 GMT

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;

Modify field value in script

Anonymous — Fri, 30 Dec 2011 00:01:16 GMT

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

Thank you very much!

Jonathan

Re: Modify field value in script

ngrunoz — Mon, 20 Mar 2017 10:59:46 GMT

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

Re: Modify field value in script

johnw — Mon, 20 Mar 2017 16:06:36 GMT

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