Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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

View solution in original post

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.