2 Replies Latest reply: Jun 2, 2012 8:21 AM by 2perform RSS

    IF Statement in script is not working well

      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
          Dave Riley

          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

            WORKS GREAT  MANY THANX FOR YOUR GREAT EXPLANATION !!!