Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IF statement using an operator that evaluates against a list

Status
Not open for further replies.

k2msl200

Technical User
Mar 7, 2013
6
US
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.






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

Ties Blom

 
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')

 
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')

 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top