topic Re: IF Statement in script is not working well in QlikView

IF Statement in script is not working well

Sat, 02 Jun 2012 08:33:29 GMT

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 !!!

Re: IF Statement in script is not working well

flipside — Sat, 02 Jun 2012 09:32:50 GMT

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.

Re: IF Statement in script is not working well

Sat, 02 Jun 2012 12:21:43 GMT

WORKS GREAT MANY THANX FOR YOUR GREAT EXPLANATION !!!