Split string field into table rows

[#1]

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.

[matt_crowther]

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

[#3 · Accepted solution]

Hi,

You can see the attached example

[#4]

Thanks everyone for answers.

[#5]

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.

[#6]

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.

[#7]

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.

[#8]

Thank you a lot!

[#9]

Hi Try with Subfield like below:

LOAD ID ,

          Invoice ,

          Subfield(Invoice ,'/') AS InvoiceID

From Source.

[#10]

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