Split data in SQL

[#1]

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.

[Anonymous · Accepted solution]

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 ';'

[#3]

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

[suniljain]

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.

[#5]

Hi,

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

Thanks,

Anju

[khadeer]

Yes u can use replace function

[#7]

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

[#8]

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