2 Replies Latest reply: Jun 4, 2016 8:35 AM by Sunny Talwar RSS

    Exclude the Future Date

    Deepanshu Chamoli

      Hello All

       

      Kindly help me in the below query for Qlik Sense:

       

      I have dates ranges from 2014-02-01 00:00:00 to 2017-12-31 00:00:00 which includes the future dates as well.

       

      I want to include the filter in LOAD statement which includes the dates from 2014-02-01 00:00:00 till today's date.


      I try to write the code in SQL as:


      where DATE(FLOOR(Active_date), 'YYYY-MM-DD') <= DATE(FLOOR(TODAY(), 'YYYY-MM-DD')


      But it gives the error.


      Please help to correct the formula.

       

      Regards

      Deepanshu

        • Re: Exclude the Future Date
          Stefan Wühl

          Can you post your complete LOAD statement and post the error message you get?

           

          In general, correctly interprete your date fields:

          Why don’t my dates work?

          Get the Dates Right

           

          Then you should be able to limit the date range in a WHERE clause:

           

          LOAD ...

          FROM ...

          WHERE Active_date <= Today(1);

           

          or create a flag in your master calendar:

           

          LOAD

               Date,

               If(Date <= Today(1), 1,0) AS ToDateFlag,

               ...

           

           

          The Master Calendar

          • Re: Exclude the Future Date
            Sunny Talwar

            It seems that you are putting this WHERE statement in the SQL statement. Which I think is the right thing to do because you will only bring those rows which are needed from the database. But the issue here is that QlikView's syntax won't work in your SQL statement because the execution is sent over to the database. So in order to make this WHERE statement to work, you would need a statement which you would use when you run it outside QlikView (for example in TOAD).