topic Split data in SQL in QlikView

Split data in SQL

Fri, 23 Jul 2010 05:36:07 GMT

Hello everyone.

I am Ann. I Started Learning to use qlikview.

I have asked for the function Split.

example:

Can see that the data are separated " ; " I want to extract data from each row below.

Result :

this SQl :

MMUsers:

LOAD "MM_Approver",
"MM_Company",
"MM_IDUser",
"MM_User";
SQL SELECT "MM_Approver",
"MM_Company",
"MM_IDUser",
"MM_User"

Please help me to do that.

Split data in SQL

Anonymous — Fri, 23 Jul 2010 06:06:00 GMT

Hi Ann,

You can use the subfield() function for that. Please see the info from the F1-help:

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)

subfield(S, ';' ,2)	returns 'cde' if S is 'abc;cde;efg'
subfield(S, ';' ,1)	returns NULL if S is an empty string
subfield(S, ';' ,1)	returns an empty string if S is ';'

Split data in SQL

Fri, 23 Jul 2010 07:47:27 GMT

Thank you, thank you I will do as you suggested.

Split data in SQL

suniljain — Fri, 23 Jul 2010 08:48:12 GMT

Dear Kallaya,

Qlikview have inbuilt feature to load data which is seperated by any character and you want upload it in qlikview.

Steps

1) Go to Script

2) Clik On Data Tab

3) Clik on Filed Data Button

4) You can select you text file and delimited it by ;

It Work fine in your case.

Regards

Sunil Jain.

Re: Split data in SQL

Wed, 20 Feb 2013 04:32:14 GMT

Hi,

Is there any way to change the default value separator from ',' in Current Selections?

Thanks,

Anju

Re: Split data in SQL

khadeer — Wed, 20 Feb 2013 05:32:45 GMT

Yes u can use replace function

Re: Split data in SQL

Wed, 20 Feb 2013 05:39:59 GMT

Hi Khadeer,

Thanks for replying!

But, I am obtaining the data via QMSAPI using GetCurrentSelections()..this concatenates the values using ','.So, if the data itself has ',', then splitting the data becomes a problem. How can I use replace function via QMSAPI?

Thanks,

Anju

Re: Split data in SQL

Thu, 28 Feb 2013 08:43:26 GMT

Hi All,

I was able to find a solution to the problem mentioned above.

Please see the below link:

http://community.qlik.com/message/316500#316500

Regards,

Anju