Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abhonsle
New Contributor III

Split values from a field

Hi All,

I have a filed called "Opportunity Status" in Salesforce. which has the below values:

"Closed - Won",

"Lost/Inactive/Invalid",

"Open",

"Open - Committed"

I want to split the value "Lost/Inactive/Invalid" to

"Closed - Won",

"Lost"

"Inactive/Invalid"

"Open",

"Open - Committed"

Can you help?

1 Solution

Accepted Solutions
stigchel
Honored Contributor

Re: Split values from a field

With SubField you can break a value into separate rows based on a delimiter. If you only want to split on the first delimiter, then replace that for another (e.g. |) and use that for the subfield delimiter

Load SubField(if(Index(Data,'/')=0,Data,Mid(Data,1,Index(Data,'/',1)-1)&'|'&Mid(Data,Index(Data,'/',1)+1,len(Data))),'|') as new;

Load * Inline [Data

"Closed - Won",

"Lost/Inactive/Invalid",

"Open",

"Open - Committed"

];

4 Replies
Not applicable

Re: Split values from a field

do you want to break into separate rows? If yes, you can use subfield function in the script break into two rows like below:

SubField(Replace(OpportunityStatus,'/I','|I'),'|') as NewFieldName

abhonsle
New Contributor III

Re: Split values from a field

Hi Srikanth,

Thank you for your reply. I however want "Inactive/Invalid" as one of the values in the filter. The above script splits the data into:

"Closed - Won",

"Lost"

"Inactive"

"Invalid"

"Open",

"Open - Committed"

stigchel
Honored Contributor

Re: Split values from a field

With SubField you can break a value into separate rows based on a delimiter. If you only want to split on the first delimiter, then replace that for another (e.g. |) and use that for the subfield delimiter

Load SubField(if(Index(Data,'/')=0,Data,Mid(Data,1,Index(Data,'/',1)-1)&'|'&Mid(Data,Index(Data,'/',1)+1,len(Data))),'|') as new;

Load * Inline [Data

"Closed - Won",

"Lost/Inactive/Invalid",

"Open",

"Open - Committed"

];

abhonsle
New Contributor III

Re: Split values from a field

Thanks a lot Piet. This is what I was looking for.

Community Browser