6 Replies Latest reply on Mar 9, 2017 5:33 AM by Cris Cook

# Calculated field using if statement with multiple values

Hi,

I have the below statement to create a calculated field. is this the correct way of writing this?

IF CONTAINS([Examcode],'MKNEL','MKNER','MKNEB') Then 'Radiology - General'

ELSEIF  CONTAINS([Examcode],'MMCSFS','MCMOP','MCAMFC','MCMFSC','MCSPS','MCSFS','MCMFS','MCORP','MCARD', 'MCVIA', 'MCRPS',

'MAOTHC', 'MCHIL', 'MCMPV', 'MCCMS', 'MCMFVC', 'MAAOW') then 'Cardiac MRI'

ELSEIF  CONTAINS([Examcode],'CARD', 'CPET', 'ETT', 'HOLT24LEAD', 'HOLT24HR', 'HOLT48HR', 'HOLT7DAY', 'RTEST', 'TILT', 'TTE',

'TTEPAED') then 'Cardiac ECHO'

ELSEIF [Refdepartmentid]='7' then 'Radiology - AMW'

ELSEIF [Refdepartmentid] <> '7' then 'Radiology - General'

ELSEIF [Refdepartmentid]='34' then 'Radiology - Rose'

ELSEIF [Refdepartmentid]='43' then 'Radiology - NEL'

ELSEIF [Refdepartmentid]='55' then 'Radiology - QMH'

END

• ###### 1. Re: Calculated field using if statement with multiple values

Hi

I think you are going to need to beak out each of the Contain portions into separate clauses - eg

IF CONTAINS([Examcode],'MKNEL') Then 'Radiology - General

elseif

IF CONTAINS([Examcode],'MKNER',) Then 'Radiology - General

elseif

IF CONTAINS([Examcode],'MKNEB') Then 'Radiology - General

....

and so on

Good luck

let me know if this worked

Jim

• ###### 2. Re: Calculated field using if statement with multiple values

You can simplify this a bit, and make it a bit more readable with some Boolean logic:

IF CONTAINS([Examcode],'MKNEL') or CONTAINS([Examcode],'MKNER') or ONTAINS([Examcode],'MKNEB') Then 'Radiology - General'

elseif ....

change "or" to "and" if it needs to meet all of the conditions and not just one

Hope this helps!

EDIT:

depending on the size of your data set, you may see some performance issues when using this field (string functions are slow and contains is usually one of the slowest). If you are using an extracted data source you can include the calculated field with the extract, either by publishing the extract after you have created this field (If using server), or Optimize Extracts . Depending on the type of data source you are connecting to, you could potentially create this with custom SQL or with a view in the database.

• ###### 3. Re: Calculated field using if statement with multiple values

Is there no simpler way than breaking up each 'CONTAIN' statement?!

Also it gives me an error 'can't compare integer and string values'...

• ###### 4. Re: Calculated field using if statement with multiple values

Hi

Yes there is another way if you want to set up and maintain a separate data file that contains the relationships between the Examcodes and the Departments

I'm thinking like an excel file that you would load as a separate data source and join with the you primary file - joining on the Examcodes

Let me know if this helps

Jim

• ###### 5. Re: Calculated field using if statement with multiple values

The excel file would have the form

 Examcode Department MKNEL Radiology - General MKNER Radiology - General MKNEB Radiology - General And So On for each of the Examcode /Department Pairs

Make Sense?

Jim

• ###### 6. Re: Calculated field using if statement with multiple values

Hi Rani,

Unfortunately there is not really an easier way to combine a multiple contains functions other than breaking it out as per the other responses.

In regards to your 'Cant compare Integer and string values' just close your integer value inside of str(), this will overcome your issue.

Kind Regards

Cris