topic Re: Delimit data in QlikView

Delimit data

rido1421 — Thu, 03 Oct 2013 10:43:44 GMT

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

The data is based on a SQl result set

Regards,

Ridhaa

Re: Delimit data

Thu, 03 Oct 2013 10:56:35 GMT

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

Re: Delimit data

rido1421 — Thu, 03 Oct 2013 11:16:24 GMT

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

Re: Delimit data

tresB — Thu, 03 Oct 2013 11:21:46 GMT

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)

Re: Delimit data

Thu, 03 Oct 2013 11:27:22 GMT

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

Re: Delimit data

rido1421 — Thu, 03 Oct 2013 11:31:06 GMT

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

Re: Delimit data

tresB — Thu, 03 Oct 2013 11:34:57 GMT

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?

Re: Delimit data

rido1421 — Thu, 03 Oct 2013 11:37:24 GMT

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