Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split string field into table rows

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.

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

You can see the attached example

View solution in original post

10 Replies
matt_crowther
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Hi,

You can see the attached example

Not applicable
Author

Thanks everyone for answers.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

Thank you a lot!

Not applicable
Author

Hi Try with Subfield like below:

LOAD ID ,

          Invoice ,

          Subfield(Invoice ,'/') AS InvoiceID

From Source.

Not applicable
Author

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