How to show blanks in lists

building reports desktopintelligence
1

Hi,

While trying to compare results of a BO query to results of a SAS query I came across some discrepencies in results and have been having problems trying to resolve the differences.

My queries have 3 “OR” conditions

If Interpreter Flag is Y
or
If language is not 1201 (English)
or
If Birth Nation not in list 1100,1301,2107,2188,7102,7104,9220

My problem arises when the field is blank. In SAS the language is not in(’ ‘,‘1201’) or birthnation is not in (’ ',‘1100’ etc)

For BO I tried coding birthnation not in list , , , ,1100,1301,2107,2188,7102,7104,9220 but still have one item showing up with no birthnation. When I view the Data results there are actually 5 items with blank birthnations.

I tried coding language as not in list , , , ,1201 but still have 9 items showing up with no language. When I view the Data results there are actually 18 items with blank language.

The overall difference between the SAS query and the BO query is 23.

Can anyone suggest a way that I can key my BO query in to eliminate everything that is blank? I originally tried blank then comma, which got rid of some of the blanks, then added 2 blanks and a comma which got rid of a few more.

Thanks for any suggestions

capri :australia: (BOB member since 2003-06-20)

2

Could you try an OR statement

Birth Nation not in list 1100,1301,2107,2188,7102,7104,9220
or
Birth Nation Is Null
or BirthNation Equal blank (actually enter a space).

You could certainly build a filter object to deal with this.

Steve Krandel :us: (BOB member since 2002-06-25)

3

You will want to add:
IS NOT NULL type comparisons for all of your objects.

You may also want to define your objects with a TRIM operation around them (in the Universe) first. Your issue is that your “blank” cells contain 1 or more blank spaces. The Trim will remove leading/trailing blanks (in in the cases that have ALL SPACES, turn the object into a NULL field).

After that… the IS NOT NULL will handle your object correctly.

trim(Language) is not null and trim(language) not in (‘1201’)

-RM

digpen :us: (BOB member since 2002-08-15)