Problem with using InList Not InList in same if statement

building reports webintelligence
1

Hi,

I am trying to put together a simple variable called National Accounts that will assign company names to a specific Siebel account key. There are 13 specific keys that need to be assigened a name, all the rest would go under independent. I’m using the function below. Using the InList command I can get everything but independent. I try to use Not InList and get an error message saying there is a missing ‘;’ before argumnet in Function ‘If’ at position 778(error WIS 10062. The word ‘Not’ is highlighted but as far as I can tell the syntax is correct. I have removed the space between Not and InList with the same results. I’ve seen other entries on here where InList and Not InList are working in Webi and need some help on how to proceed.

=If([Siebel Parent Key L4] InList(“1+22F+14274”);“Company1”;If([Siebel Parent Key L4] InList(“1+1IY+2672”);“Company2”;If([Siebel Parent Key L4] InList(“1+1IS+2106”);“Company3”;If([Siebel Parent Key L4] InList(“1+1IP+1077”);“Company4”;If([Siebel Parent Key L4] InList(“1+1IV+2499”);“Company5”;If([Siebel Parent Key L4] InList(“1-6G1E-6”);“Company6”;If([Siebel Parent Key L4] InList(“1-4444-18”);“Company7”;If([Siebel Parent Key L4] InList(“1+1I7+852”);“Company8”;If([Siebel Parent Key L4] InList(“1+1I4+1233”);“Company9”;If([Siebel Parent Key L4] InList(“1+1I4+3153”);“Company10”;If([Siebel Parent Key L4] InList(“1-2WXMXT”);“Company11”;If([Siebel Parent Key L4] InList(“1+N72+18”);“Company12”;If([Siebel Parent Key L4] InList(“1+1IP+1532”);“Company13”;If([Siebel Parent Key L4] Not InList(“1+1IP+1532”;“1+22F+14274”;“1+1IY+2672”;“1+1IS+2106”;“1+1IP+1077”;“1+1IV+2499”;“1-6G1E-6”;“1-4444-18”;“1+1I7+852”;“1+1I4+1233”;“1+1I4+3153”;“1-2WXMXT”;“1+N72+18”;“1+1IP+1532”)“Independent”)))))))))))))))))

ck1938 (BOB member since 2008-06-09)

2

In your not list syntax end ; is missing. Try this…

=If([Siebel Parent Key L4] InList(“1+22F+14274”);“Company1”;If([Siebel Parent Key L4] InList(“1+1IY+2672”);“Company2”;If([Siebel Parent Key L4] InList(“1+1IS+2106”);“Company3”;If([Siebel Parent Key L4] InList(“1+1IP+1077”);“Company4”;If([Siebel Parent Key L4] InList(“1+1IV+2499”);“Company5”;If([Siebel Parent Key L4] InList(“1-6G1E-6”);“Company6”;If([Siebel Parent Key L4] InList(“1-4444-18”);“Company7”;If([Siebel Parent Key L4] InList(“1+1I7+852”);“Company8”;If([Siebel Parent Key L4] InList(“1+1I4+1233”);“Company9”;If([Siebel Parent Key L4] InList(“1+1I4+3153”);“Company10”;If([Siebel Parent Key L4] InList(“1-2WXMXT”);“Company11”;If([Siebel Parent Key L4] InList(“1+N72+18”);“Company12”;If([Siebel Parent Key L4] InList(“1+1IP+1532”);“Company13”;If([Siebel Parent Key L4] Not InList(“1+1IP+1532”;“1+22F+14274”;“1+1IY+2672”;“1+1IS+2106”;“1+1IP+1077”;“1+1IV+2499”;“1-6G1E-6”;“1-4444-18”;“1+1I7+852”;“1+1I4+1233”;“1+1I4+3153”;“1-2WXMXT”;“1+N72+18”;“1+1IP+1532”);“Independent”)))))))))))))))))

Hope this helps you…!!

srijaggu :us: (BOB member since 2007-09-07)

3 
"Independent")))))))))))))))))

Shouldn’t your If not in list part read:-

;"Independent")))))))))))))))))

Mak 1 :uk: (BOB member since 2005-01-06)

4

Hi All. Thanks for your help. One of our consultantswas able to track down the problem and get the correct syntax from the Business Objects support site. The syntax that works in the statement is:

If( Not( [Siebel Parent Key L4] InList (“1+1IP+1532”;“1+22F+14274”;“1+1IY+2672”;“1+1IS+2106”;“1+1IP+1077”;“1+1IV+2499”;“1-6G1E-6”;“1-4444-18”;“1+1I7+852”;“1+1I4+1233”;“1+1I4+3153”;“1-2WXMXT”;“1+N72+18”;“1+1IP+1532”) ) ; “Independent”))

Give or take a few parentheses at the end. Here’s the note that gave him what he needed to figure it out:

Note
It is the combination of object + InList that returns a boolean value, not InList alone. You must be aware of this when combining InList with other boolean operators. For example, you use NOT like this:

NOT ([object] InList(list) )

The syntax [object] NOT InList(list) is incorrect and returns an error.

ck1938 (BOB member since 2008-06-09)