topic manipulating strings in QlikView

manipulating strings

thomaswrieck — Thu, 01 Jul 2010 12:15:43 GMT

Hi,

I'm looking for a clever way to do the following :

I have strings like this

AB8345VC0123

and I need to have them converted into

AB-8345-VC-0123

So, it's about putting "-" between letters and number blocks ... there is no pattern in the original string to rely on ... any idea how to do this ?

Thanks a lot

Thomas

manipulating strings

Thu, 01 Jul 2010 12:35:49 GMT

Hi Thomas

If the field always look like that with 2 letter 4 number 2 letter 4 number, you can use this.

=left(Field,2) & '-' & right(left(Field,6),4) & '-' & right(left(Field,8),2) & '-' & right(Field,4)

Anders

manipulating strings

Thu, 01 Jul 2010 13:05:26 GMT

Hi Thomas,

I don't know how much this will works. This is just an idea to try by yourself.

I have a Logic. First get the length of the String and loop it in Array. If the digit is alphabet then have the statement like this.

=KeepChar(String,'ABCDEFGHIJKLMENOPQRSTUVWXYZ')

Else if the digit is Number have the statment like this.

='-'&KeepChar(String,'1234567890')

I hope you can try this with the Help of API Guide.

manipulating strings

thomaswrieck — Thu, 01 Jul 2010 13:17:35 GMT

yep .. similar to what I was ending up with .... len(string, then if(char is A-Z and next char isr 0-9) -> replace(firstchar, firstchar-) ... tanks a lot

manipulating strings

Thu, 01 Jul 2010 15:08:03 GMT

Hi is the string always (AB8345VC2013) 12 characters long?

becuase then i can write you a piece of code to sort this out....

Thanks,

manipulating strings

thomaswrieck — Thu, 01 Jul 2010 15:14:13 GMT

Hi,

I'm not sure yet what the pattern for these strings are ... I guess there is none so I'm preparing for something very generic ... no matter how long, no matter how the pattern is ... the only thing that is very sure is the usage of (A-Z) and (0-9) combinations ... no other chars

Thanks a lot

Thomas

manipulating strings

johnw — Thu, 01 Jul 2010 21:53:41 GMT

Here's a weird solution. Generate a mapping table mapping things like 7B to 7-B, and X5 to X-5, with every possible combination. Then mapsubstring() your data using that table. It appears that you have to do it twice to get everything, but it seems to work. See attached.

manipulating strings

thomaswrieck — Fri, 02 Jul 2010 08:40:18 GMT

🙂 wow excellent ... I was hesitating doing something like this cause you need to prepare this mapping table ...

meanwhile I ended up doing it with the following code , still evaluating if it works for every thinkable combination ...

SET vString='Afdfdgsd-fX456G-H78GH';

SET vNew='Afdfdgsd-fX456G-H78GH';

/* ----------------- Removing Non-Letters and Non-Numbers --------------------------- */

if (findoneof('$(vString)','-')>0) then /* Insert more chars here in <findeoneof> to be removed */

LET vString=purgechar('$(vString)','-');

LET vNew='$(vString)';

end if

/* ---------------------------------------------------------------------------------- */

LET v_L=2*(len('$(vString)')); /* String is growing for each inserted '-',

worst case is a string with alternating

letters and numbers */

LET v_Skip=0;

/* ------------------- Start evaluating/modifieing string --------------------------- */

for i=1 to $(v_L)

LET vString='$(vNew)';

LET vs1=IsText(mid('$(vString)',$(i)+$(v_Skip),1));

LET vs2=IsText(mid('$(vString)',$(i)+$(v_Skip)+1,1));

LET v3=left('$(vString)',$(i)+$(v_Skip));

/* If char1 is A-Z and char2 is 0-9 (or the other way around).

This is the point to do something */

if(($(vs1)<0 and $(vs2)>-1) or ($(vs1)>-1 and $(vs2)<0) ) then

/* Replaces the <char> with <char-> and concat the rest of the so far unmodified piece of the string */

LET vNew=replace('$(v3)','$(v3)','$(v3)'&'-')&mid('$(vString)',$(i)+1,$(v_L)+1) ; // is AD-56GH78GHB

LET v_Skip=1;

else

LET v_Skip=0;

end if

manipulating strings

thomaswrieck — Sun, 04 Jul 2010 13:22:29 GMT

Hi,

just wanted to show what my final solution for this is ... maybe it is useful for somebody as well ...

Thanks a lot

Thomas