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

    Date format problem

    Johan Adolfsson



      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:


      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?



        • 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,


            • Date format problem
              Johan Adolfsson

              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,