Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
abhonsle
Creator
Creator

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
Partner
Partner

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"

];

View solution in original post

4 Replies
Not applicable

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
Creator
Creator
Author

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
Partner
Partner

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"

];

View solution in original post

abhonsle
Creator
Creator
Author

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