Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!!
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.
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.
WORKS GREAT MANY THANX FOR YOUR GREAT EXPLANATION !!!