Using IF/AND/OR together

[alwayslearning]

Hi

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

[sunny_talwar]

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

[Anil_Babu_Samineni]

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

[swuehl]

" 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.

[swuehl]

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

];

[Anil_Babu_Samineni]

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

[alwayslearning · Accepted solution]

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

[alwayslearning]

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)

[swuehl]

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