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
IF CONTAINS([Examcode],'MKNER',) Then 'Radiology - General
IF CONTAINS([Examcode],'MKNEB') Then 'Radiology - General
and so on
let me know if this worked
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'
change "or" to "and" if it needs to meet all of the conditions and not just one
Hope this helps!
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.
Is there no simpler way than breaking up each 'CONTAIN' statement?!
Also it gives me an error 'can't compare integer and string values'...
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
I should have added this
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
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.