topic Re: Using IF/AND/OR together in QlikView

Using IF/AND/OR together

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

I need help in writing a formula in the script which combines looking a various columns of data

e.g.

Report Date, Start Date, a, b, c

01/01/2017 20/01/2017 1 0

01/01/2017 30/12/2016

using the table above I need to write a statement which firstly compares Report Date with Start Date and only consider anything where Start Date is after Report Date. Next I need check columns a,b,c for blanks and if they are all blank then they highlighted. Which ever rows meet these criteria I am flagging as 1 and if it does not then 0.

I am thinking some kind of If /AND /OR statement like the following but need help with the syntax.

IF (Start Date > Report Date AND (a='' or b='' or c='') , 1, 0) as Nullvalue

Thanks

Re: Using IF/AND/OR together

sunny_talwar — Wed, 08 Feb 2017 10:26:16 GMT

May be this:

If(Start Date > Report Date

If(Len(Trim(a)) = 0 or Len(Trim(b)) = 0 or Len(Trim(c)) = 0,1,0), 0) as Nullvalue

Re: Using IF/AND/OR together

Anil_Babu_Samineni — Wed, 08 Feb 2017 10:30:56 GMT

Or else This

If([Start Date] > [Report Date] And (Len(Trim(a)) = 0 or Len(Trim(b)) = 0 or Len(Trim(c)) = 0),1,0)) as Nullvalue

Re: Using IF/AND/OR together

swuehl — Wed, 08 Feb 2017 10:39:06 GMT

" Next I need check columns a,b,c for blanks and if they are all blank then they highlighted."

If you want to check for all three field values being blank, use AND instead of OR logic.

Re: Using IF/AND/OR together

swuehl — Wed, 08 Feb 2017 10:45:15 GMT

And if you want to check for all fields being blank, you can also do this like without any AND

LOAD * ,

-([Start Date]>[Report Date] xor IsNum(alt(1*a,1*b,1*c,'no number')) ) as Flag

INLINE [

Report Date, Start Date, a, b, c

01.01.2017, 20.01.2017, 1 , , 0

01.01.2017, 30.12.2016,,,

01.01.2017, 20.01.2017, , ,

];

edit:

Well, above expression is not equivalent to this (which I think is what you wanted):

LOAD * ,

-([Start Date]>[Report Date] and IsText(alt(1*a,1*b,1*c,'no number')) ) as Flag

INLINE [

Report Date, Start Date, a, b, c

01.01.2017, 20.01.2017, 1 , , 0

01.01.2017, 30.12.2016,,,

01.01.2017, 20.01.2017, , ,

01.01.2017, 30.12.2016,,1,2

];

Re: Using IF/AND/OR together

Anil_Babu_Samineni — Wed, 08 Feb 2017 10:51:01 GMT

swuehl

-([Start Date]>[Report Date] xor IsNum(alt(1*a,1*b,1*c,'no number')) ) as Flag

How - Will consider here, I doubt, What was the process running Background. Try to explain while having Free time

Re: Using IF/AND/OR together

alwayslearning — Wed, 08 Feb 2017 10:52:15 GMT

Thanks Stefan

That is what I needed, I just needed to change what I already had to AND from OR

if([Start Date] >= [Report Date], if( a='' and b = '' and c='', 1,0),0) as NullValue

Re: Using IF/AND/OR together

alwayslearning — Wed, 08 Feb 2017 11:02:22 GMT

Hi,

I wanted to ask by using Len(Trim(field) does this account for Null and Blanks? As currently I am using a='' as they are blanks rather than nulls but if they were null I would use Isnull(a)

Re: Using IF/AND/OR together

swuehl — Wed, 08 Feb 2017 11:38:53 GMT

Len(Trim(FIELD)) is basically checking for both cases, blank (or value containing only spaces) and NULL, in FIELD.