IF statement using an operator that evaluates against a list

[k2msl200]

Cognos Experts,

I need a little help with an expression. This Expression works, IF ([Phr NPI] ='1073861118') THEN ('PREFERRED") ELSE ('PREFERRED), but instead of using ='1073861118', I need it to evaluate against a list of Phr NPI's rather than equaling just one.

Basically I need something similar to this:
IF ([Phr NPI] InList('1699857490', '1073861118', '1881961662', '1770648933', '1467471490') THEN ('PREFERRED") ELSE ('NON-PREFERRED).

I know "InList" is not a COGNOS operator, but I used it to show what I'm trying to accompolish. I tried using the In_Range and Contains operators , but I get an error.

 

[blom0344]

Why not use 'in' instead of 'inlist'. No idea where you are working with though..

Ties Blom

 

[k2msl200]

Sorry. I am working with Business Insight Advanced 10.1.

What I am trying to accompolish is to create a custom measure that will return "Preferred" for these location ID's '1699857490', '1073861118', '1881961662', '1770648933', '1467471490', and "Non-Preferred" for all others

I tried using the below formula, but when I include "In" in place of the equals operator, I get an error. Perhaps I am going about this all wrong?

If(Phr NPI]='1073861118') THEN ('PREFERRED') ELSE ('NON-PREFERRED')

 

[k2msl200]

I was able to work out the solution to my issue. Had to tinker around with it a bit for it to work.

If([Phr NPI]IN('1699857490', '1073861118', '1881961662', '1770648933', '1467471490', '1134372881', '1922035856', '1316205537', '1740275973')) THEN ('PREFERRED') ELSE ('NON-PREFERRED')

 

[blom0344]

With just 5 values, can't yo not just 'or' like:

If(([Phr NPI]='1073861118') or ([Phr NPI]='1073861118')) THEN ('PREFERRED') ELSE ('NON-PREFERRED')

= example for 2 values

Ties Blom