topic Re: is not null OR is not null in QlikView

is not null OR is not null

Mon, 11 May 2015 14:30:33 GMT

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

Re: is not null OR is not null

mrossoit — Mon, 11 May 2015 14:34:03 GMT

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

Re: is not null OR is not null

mambi — Mon, 11 May 2015 14:42:24 GMT

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

Re: is not null OR is not null

sunny_talwar — Mon, 11 May 2015 14:48:45 GMT

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

Re: is not null OR is not null

Mon, 11 May 2015 15:05:05 GMT

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

Re: is not null OR is not null

Mon, 11 May 2015 15:06:01 GMT

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

Thanks for your time guys - any other ideas?

Ella

Re: is not null OR is not null

swuehl — Mon, 11 May 2015 15:19:07 GMT

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

Re: is not null OR is not null

mambi — Mon, 11 May 2015 15:23:51 GMT

i think we need more explanations

Re: is not null OR is not null

Mon, 11 May 2015 15:30:58 GMT

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!

Re: is not null OR is not null

Mon, 11 May 2015 15:44:06 GMT

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

Ella