Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rido1421
Contributor II

Delimit data

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

1 Solution

Accepted Solutions
rido1421
Contributor II

Re: Delimit data

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

7 Replies
Not applicable

Re: Delimit data

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
Contributor II

Re: Delimit data

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

MVP
MVP

Re: Delimit data

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)

Not applicable

Re: Delimit data

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

rido1421
Contributor II

Re: Delimit data

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

MVP
MVP

Re: Delimit data

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
Contributor II

Re: Delimit data

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

Community Browser