Combine ifelse + locate; is it possible?

Question & Answer
1

Hello,

I’m trying to create a calculated field where the logic basically says if X the string contains X, then put X. Is that possible? This is an example of the string I’m trying to do this on:

‘Retrospective,Doc - Communication Email,TS - Medium LP,TC - Process Improvement’ > so for example in my logical function if the example contains ‘Process’ then add ’ Process Improvement’.

Thanks in advance for any help.

2

Yes this should be possible. I’m not entirely clear what you are trying to do, for example if your column with strings is ‘X’ and you want to return the string ‘Process Improvement’ if the string ‘X’ has ‘Process’ in it, then something like this:

ifelse(locate({X},‘Process’)>0,‘Process Improvement’,’’)

Note that locate is case sensitive so you may wish to use lower({X}) and ‘process’. If you’re trying to combine two strings then concat() can do this.

1 Like
3

Hi Simon,

Thank you so much for taking the time of replying to my question.

That worked perfectly! Last question, is it possible to add several conditions for the if? I tried this but is giving me an error:
ifelse(locate({Labels},‘Process’)>0,‘Process Improvement’,’’),ifelse(locate({Labels},‘Support’)>0,‘Support Activities’,’’)

4

Yes it is possible but the condition would look like this. Keep also in mind that you don’t use special characters or Chinese/Japanese in the code since it can create trouble on CLI level if you like to move datasets within different accounts for logic within the dashboard itself it should be fine.

ifelse(
/* add prefix for sorting of case status*/
{status[case]} = "Discarded", "00 - Discarded", 
{status[case]} = "Draft", "01 - Draft",
{status[case]} = "Ready", "02 - Ready",
{status[case]} = "Submitted", "03 - Submitted",
{status[case]} = "Approved-OEM", "04 - Approved-OEM",
{status[case]} = "Approved", "05 - Approved",
{status[case]} = "Rejected", "06 - Rejected",
{status[case]} = "Rejected-X", "07 - Rejected-X",
{status[case]} = "Escalated-X", "08 - Escalated-X", 
{status[case]} = "Closed-X", "09 - Closed-X", 
{status[case]} = "Closed", "10 - Closed", 
{status[case]})
5

Hi,

Thanks so much for taking the time to reply to this.

I didn’t quite understand what I had to replace from the code you shared given the string I have. I need to categorize strings based on tags to create a pie chart based on those categories: Doc - SOP,TS - Medium LP, TC - Process Improvement (each comma separated value is a category).

The solutions that SimonW provided worked perfectly, but I need to add more conditions to the if. Is that possible in QS?

Thanks in advance

6

Update
I was able to do it, special thanks to SimonW. In addition I recommend seeing this from 4:32 forward. How to use String Functions within Calculated Fields

1 Like
7

The correct format would be the following since you already use ifelse.

ifelse(
   locate({Labels},‘Process’)>0,‘Process Improvement’,
   locate({Labels},‘Support’)>0,‘Support Activities’,
<<add other conditions followed by the default value in your case empty string>>
’’)
1 Like