topic Split String in parts in QlikView

Split String in parts

Wed, 25 Jul 2012 09:12:10 GMT

Who can help me.

I have a string, named Service.
I want to devide the string into three separate parts, named: Area, Function and Systeem.

The structure from Service is as follows:
Layout Service:Number,Space,Area,-,Number,Space,Function,-,Number,Space,Systeem

For example:5 D PIER-02 LATERALS-50240 LATERALS NOORD
Area:D PIER
Function:LATERALS
Systeem:LATERALS NOORD

Next example:3 TERMINAL 2-33 ALT LOSINSTALLATIE (MUM)-53301 ALT LOSINSTALLATIE 21
Area:TERMINAL 2
Function:ALT LOSINSTALLATIE (MUM)
Systeem:ALT LOSINSTALLATIE 21

I want to use the script in the loadscript.

Can someone help me with this.

Thanks in advance,

Frans

Re: Split String in parts

Wed, 25 Jul 2012 09:22:26 GMT

Try using FindOneOf() this will let you find the position of the - delimeter you have and then it is just using a mix of left, mid, right and len to split it down for you.

Re: Split String in parts

swuehl — Wed, 25 Jul 2012 09:29:33 GMT

Maybe like this:

LOAD *,

mid(subfield(TXT,'-',1),index(TXT,' ')) as Area,

mid(subfield(TXT,'-',2),index(TXT,' ',2)) as Function,

mid(subfield(TXT,'-',3),index(subfield(TXT,'-',3),' ')) as System

INLINE [

TXT

5 D PIER-02 LATERALS-50240 LATERALS NOORD

3 TERMINAL 2-33 ALT LOSINSTALLATIE (MUM)-53301 ALT LOSINSTALLATIE 21

];

Re: Split String in parts

Wed, 25 Jul 2012 09:47:34 GMT

Thanks it works, i convert the second code from

mid(subfield(TXT,'-',2),index(TXT,' ',2)) as Function to

mid(subfield(TXT,'-',2),index(subfield(TXT,'-',2),' ')) as Function.

After that Function works also correct.

Thank you.

Frans