IF Statement in script is not working well

[#1]

Hi all,

This is what i want to achieve

  in case OPNTYPE = K AND   EINDDAT = > TODAY+150 or  EINDDAT is NULL  EINDMUT should be  INGDAT

In case OPNTYPE = D AND   EINDDAT = > TODAY+150 or  EINDDAT is NULL  EINDMUT should be  INGDAT+3

In all other cases the EINDDAT is correct and EINDMUT should be the same as EINDDAT

Here for I use this statement in my script

LOAD

           PlannrAfd,

           Date(

           If(

           If(OPNTYPE='K' AND ISNULL(EINDDAT) OR EINDDAT> Today()+150,INGDAT+3,EINDDAT),

           If(OPNTYPE='D' AND ISNULL(EINDDAT) OR EINDDAT> Today()+150,INGDAT,EINDDAT)),

          'dd-MM-YYYY hh:mm') as EINDMUT

But is is not working well as the result of

PLANNR 123 OPNTYPE=K INGDAT = 6-12-2012,  EINDDAT 31-12-2999 is resulting in EINDMUT 06-12-2011 ,

Which is corresponding with the statement for OPNTYPE 'D'

What am I doing wrong??

Gratefull greeetings to those who can help !!!

[flipside · Accepted solution]

Hi 2perform,

There are a couple of things wrong with the nested if statement as far as I can see. Firstly the logic test needs changing by wrapping another set of brackets round the "OR" part ...

OPNTYPE='K' AND (ISNULL(EINDDAT) OR EINDDAT> Today()+150)

... then you need to structure the full statement like this ...

if( {logic test 1} , {value if true}, if( {logic test 2} , {value if true}, {value if false}) ) as fieldname

I haven't tested this but I think the statement should be something like ...

LOAD

     PlannrAfd,

     Date(

     If(OPNTYPE='K' AND (ISNULL(EINDDAT) OR EINDDAT> Today()+150),INGDAT+3,

           If(OPNTYPE='D' AND (ISNULL(EINDDAT) OR EINDDAT> Today()+150),INGDAT,EINDDAT))

        'dd-MM-YYYY hh:mm') as EINDMUT

Hope this helps

flipside

PS Just noticed your original case specification and query seem to have swapped the INGDAT & INGDAT+3 values, so not sure which you need - you might need to swap them over in my code.

[#3]

WORKS GREAT  MANY THANX FOR YOUR GREAT EXPLANATION !!!