topic Re: Split string field into table rows in QlikView

Split string field into table rows

Tue, 19 Jul 2011 08:33:21 GMT

Hi,

I am facing the following problem.

I have table A like this:

ID I INVOICE

1 I S200/M250/R35/

2 I M1687/A35/D879/F689/

3 I B33625/S55/

For each "ID", I need to split the "INVOICE" string field as shown below :

ID I INVOICE

1 I S200

1 I M250

1 I R35

2 I M1687

2 I A35

2 I D879

..............................

3 I S55

Any idea how to do it?

Best regards.

Re: Split string field into table rows

matt_crowther — Tue, 19 Jul 2011 08:55:19 GMT

May not be the most efficient way to solve this but...:

In the script; firstly split the Invoice field in to its parts using something like left(Invoice,index(Invoice,'/')-1) and so on to give you a table (Data) like:

ID | Invoice | InvoiceA | InvoiceB

1 S200 M250 R35

...

Now perform a Concatenate load from your table back onto your table something like:

Concatnate (Data)

Load ID,

InvoiceA as Invoice

Resident Data;

Drop Field InvoiceA;

Repeat this for InvoiceB and C, D, E or as many parts as you have.

That should result in the table you need.

Alternatively: you can at this point perfom a Cross Table load to achieve teh same effect (see the Help file).

The script hasn't been checked.

Hope that helps,

Matt - Visual Analytics Ltd

Re: Split string field into table rows

Tue, 19 Jul 2011 08:59:19 GMT

Hi,

You can see the attached example

Split string field into table rows

Tue, 19 Jul 2011 09:22:02 GMT

Thanks everyone for answers.

Re: Split string field into table rows

Tue, 28 Jan 2014 13:49:24 GMT

Hello, could you post the script from example.qvw here, I just can't open this project due to my personal edition of QlikView.

Thank you.

Re: Split string field into table rows

Tue, 28 Jan 2014 13:51:13 GMT

Hello, Lulian, could please share the script that solved you porblem, I have the same task. Example.qvw project does not open in my personal QV edition.

Re: Split string field into table rows

Tue, 28 Jan 2014 14:15:38 GMT

Hello Marat,

This is the solution :

Tab1:

Load

ID ,

Invoice ,

NewInvoice

Where Len ( NewInvoice ) > 0

;

Load

ID ,

Invoice ,

SubField( Invoice,'/') as NewInvoice

;

Load * Inline

[ ID , Invoice

1 , S200/M250/R35/

2 , M1687/A35/D879/F689/

3 , B33625/S55/

] ;

Regards.

Re: Split string field into table rows

Tue, 28 Jan 2014 14:17:20 GMT

Thank you a lot!

Re: Split string field into table rows

Tue, 28 Jan 2014 14:20:45 GMT

Hi Try with Subfield like below:

LOAD ID ,

Invoice ,

Subfield(Invoice ,'/') AS InvoiceID

From Source.

Re: Split string field into table rows

Tue, 28 Jan 2014 14:26:27 GMT

Thanks, I've tried in such way (that other memebers also recommended me) and got fine result.

Re: Split string field into table rows

Anonymous — Thu, 24 Jul 2014 05:57:22 GMT

Hi,

Thanks For u r example

@ mona_qlik