is not null OR is not null

[#1]

Hi All,

Am trying to create an audit report looking at fields that have been amended from NULL to anything else. I need a report that only shows me a record where any of the many fields being audited are not null. I tried in my load script to create a field 'Audit' where NULL was 1, and not NULL 2, but this only works for one field - add another field to that statement and it's an AND - i.e. only shows me where both fields are not NULL. I tried adding an OR to the statement, but I can't get this to work. The script I tried was:

LOAD

*,
if(
(Vendors.ACCTS_PAY_CODE_COMBINATION_ID = 'NULL') or
(Vendors.ALLOW_AWT_FLAG = 'NULL') or
(Vendors.CUSTOMER_NUM = 'NULL') or
(Vendors.ATTRIBUTE2 = 'NULL'),1, 2) as 'Audit';

but this only returns a value of '1' for 'Audit'.

I don't want to have a calculated field for every field as the performance will be pityful - there must be a way to create a flag in the load script?

Thanks

Ella

[mrossoit]

Use

isnull(your_field)

LOAD

*,
if(
(isnull(Vendors.ACCTS_PAY_CODE_COMBINATION_ID) or
isnull(Vendors.ALLOW_AWT_FLAG) or
isnull(Vendors.CUSTOMER_NUM) or
isnull(Vendors.ATTRIBUTE2),1, 2) as 'Audit';

Regards

MR

[mambi]

Hi, you can use this :

LOAD
*,
if (
( len(trim(Vendors.ACCTS_PAY_CODE_COMBINATION_ID))  = 0) or
(len(trim(Vendors.ALLOW_AWT_FLAG )) =0 ) or
(len(trim(Vendors.CUSTOMER_NUM )) = 0 ) or
(len(trim(Vendors.ATTRIBUTE2)) = 0),1, 2
)  as 'Audit';

[sunny_talwar]

I am not sure what exactly are your trying to flag here:

Goal: Flag when both are null with 1 and if even one of them is not null then it should be 2?

Try this:

if(
(Len(Trim(Vendors.ACCTS_PAY_CODE_COMBINATION_ID)) = 0) or
(Len(Trim(Vendors.ALLOW_AWT_FLAG)) = 0) or
(Len(Trim(Vendors.CUSTOMER_NUM)) = 0) or
(Len(Trim(Vendors.ATTRIBUTE2)) = 0), 1, 2) as 'Audit';

Goal: Flag when either one of them or both are null with 1, otherwise 2?

Try This:

if(
(Len(Trim(Vendors.ACCTS_PAY_CODE_COMBINATION_ID)) <> 0) or
(Len(Trim(Vendors.ALLOW_AWT_FLAG)) <> 0) or
(Len(Trim(Vendors.CUSTOMER_NUM)) <> 0) or
(Len(Trim(Vendors.ATTRIBUTE2)) <> 0), 2, 1) as 'Audit';

[#5]

If i use either of the suggestedl functions it only brings back a value of 2 for 'Audit'. I have set up examples of the perfect and imperfect record so I know that if the logic is correct it will bring back both a 1 and  2. Am not sure if you can see the attached screen shot, but it shows the possible values for the fieldsa

the goal is to flag when 1 or more of the fields is not null.

Thanks for your time guys - any other ideas?

Ella

[#6]

If i use either of the suggestedl functions it only brings back a value of 2 for 'Audit'. I have set up examples of the perfect and imperfect record so I know that if the logic is correct it will bring back both a 1 and  2. Am not sure if you can see the attached screen shot, but it shows the possible values for the fieldsa

the goal is to flag when 1 or more of the fields is not null.

Thanks for your time guys - any other ideas?

Ella

[swuehl · Accepted solution]

Maybe like

if(
(Vendors.ACCTS_PAY_CODE_COMBINATION_ID <> 'NULL') or
(Vendors.ALLOW_AWT_FLAG <> 'NULL') or
(Vendors.CUSTOMER_NUM <> 'NULL') or
(Vendors.ATTRIBUTE2 <> 'NULL'),2,1) as 'Audit';

[mambi]

i think we need more explanations

[#9]

This looks good - it's pulling back 1 and 2 for 'Audit' - let me do some more testing and I'll confirm for you - thank you for this!

[#10]

I've tested it, it works great - thank you!

Ella