Delimit data

[rido1421]

Hi All

I have a result set with the below reason code sting, I would like to delimit the data based on the >> how can i do this in qlikview?

|Reason code                               |   Date           |  Agent    |

| Shop1>>Help Desk>>Enquiry|  2013-10-01 |      John   |       

I would like to the delimit the reason code column like below

|Reason code 1|  |Reason Code 2  | | Reason Code 3|

|        Shop1        |         Help Desk      | |            Enquiry  |

The data is based on a SQl result set

Regards,

Ridhaa

[#2]

Hi Ridhaa,

You could do that in the SQL as you load in, but in the load script, you can use SubField to explode out the results.

Eg SubField([Reason code],'>>') as [Reason Codes]

Kind Regards,

Dave

[rido1421 · Accepted solution]

Hi Dave

Using it this way I would get the 3rd level reason code , how would I get the 1st and second levels? Is there a way to do that?

Regards,

Ridhaa

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

[tresesco]

Subfield() has got three parameters; third one is the substring number. If you don't give that, in the script it will actually generate one record for each sub-strings.

If you want specifically first or second sub-string, you have to provide the third parameter, like:

SubField([Reason code],'>>' ,1) 

SubField([Reason code],'>>',2)

[#5]

If you leave the third parameter off, you get all three reason codes under one column. I find that quite useful.

[rido1421]

Thanks guys it works perfectly 🙂 , for future reference ... it is limited to 3 substrings?

[tresesco]

No such limitation; as many as would be there in the greater string. One thing, possibly you have marked your own post as answered by mistake ! right?

[rido1421]

Still new to the Forum ☺

Thanks

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf