5 Replies Latest reply: May 24, 2017 8:57 AM by Michael Solomovich RSS

    Cannot filter data based on date in qliksense

    Sagar Naik

      So, here's a simple query i loaded in the dataload editor.

       

      SELECT

      id as ID,

      name as CompleteName,

      age as Age,

      date(s.dateofbirth) as Date

       

      FROM studentDetail sd
      INNER JOIN StudAge s on s.studid=s.id

      WHERE date(s.dateofbirth) BETWEEN '04/01/2016' AND '03/31/2017';

       

      It doesn't gets executed. Primarily because it isn't allowing me to filter based on the date.
      Can anyone fix this code or throw some pointers?

      Also, in the data load editor, I've made the following changes:

      SET DateFormat='MM/DD/YYYY';

      SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

        • Re: Cannot filter data based on date in qliksense
          Sunny Talwar

          Which database are you pulling this information from?

            • Re: Cannot filter data based on date in qliksense
              Sagar Naik

              postgresql.
              the data connections are working fine. I can write queries fine except for the filtering part.
              my problem lies specifically with filtering using "where" on the date column.

                • Re: Cannot filter data based on date in qliksense
                  Michael Solomovich

                  Qlik setting and Qlik syntax are not used by SQL.  If it is postgresql, you have to use its syntax.  Quick Google search says it is probably:

                   

                  SELECT

                  id,

                  name,

                  age,

                  s.dateofbirth

                  FROM studentDetail sd
                  INNER JOIN StudAge s on s.studid=s.id               // this line is wrong, one of s is sd, probably sd.id

                  WHERE s.dateofbirth >= '2016-04-01'

                       AND s.dateofbirth <= '2017-03-31'

                    • Re: Cannot filter data based on date in qliksense
                      Sagar Naik

                      Yes, sorry about the inner join mistake, i typed it instead of copy-pasting.
                      The correct code is printed at the bottom.
                      The same syntax works fine in Postgres and in QlikSense too.
                      The problem occurs when i try to filter my result set based on the date selection.

                       

                      E.g.:
                      WHERE date(s.dateofbirth) BETWEEN '04/01/2016' AND '03/31/2017';

                      ------ This particular part throws the following error:

                       

                      9:44:17 TT
                      Connector reply error: ***

                      Without the WHERE condition for filtering the date, it works perfectly fine.

                      My Question is :
                      How can i filter my result set based on the Date selection?
                      E.g: WHERE s.dateofbirth< '06/10/2000';

                       

                       

                       

                      SELECT

                      id as ID,

                      name as CompleteName,

                      age as Age,

                      date(s.dateofbirth) as Date

                       

                      FROM studentDetail sd
                      INNER JOIN StudAge s on s.studid=sd.id

                      WHERE date(s.dateofbirth) BETWEEN '04/01/2016' AND '03/31/2017';

                       

                      It doesn't gets executed. Primarily because it isn't allowing me to filter based on the date.
                      Can anyone fix this code or throw some pointers?

                      Also, in the data load editor, I've made the following changes:

                      SET DateFormat='MM/DD/YYYY';

                      SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';