2 Replies Latest reply: Nov 12, 2011 8:59 AM by Johan Adolfsson RSS

    Date format problem

      Hi!

       

      I am having problems formatting a date while loading. No errors, but Qlikview doesn't format it correctly.

      I am reading dates from an Excel spreadsheet. All dates in that Excel document are in YYYY-MM-DD format.

      When I am loading my qv document I want to change all dates after October 31 2011 to October 31 2011.

      In my script it looks like this:

      LOAD.....

      Date(if(date_to>'2011-10-31',date_to='2011-10-31',date_to)) as date_to

      ...

       

      Qlikview changes all matching dates to 1899-12-30.

      Why...what am I doing wrong?

       

      Thanks,

        • Date format problem
          Stefan Wühl

          Your THEN statement is another condition, not an assignement, in this case, so it will return 0, which means 1899-12-30.

           

          Just use the literal instead, the assignment is taking place within the load, i.e.

          ...

          Date(if(date_to>'2011-10-31', '2011-10-31', date_to)) as date_to

          ...

           

          Hope this helps,

          Stefan

            • Date format problem

              Thanks Stefan. Very helpful.

              I knew I was doing something wrong. I didn't know that QlikView would interpret that as another condition when all I wanted to do was to assign a value to the variable date_to. But now I know.

               

              Thanks again,

              Johan