Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Cannot filter data based on date in qliksense

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';

5 Replies
sunny_talwar
Not applicable

Re: Cannot filter data based on date in qliksense

Which database are you pulling this information from?

Not applicable

Re: Cannot filter data based on date in qliksense

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.

mov
Not applicable

Re: Cannot filter data based on date in qliksense

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'

Not applicable

Re: Cannot filter data based on date in qliksense

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';

mov
Not applicable

Re: Cannot filter data based on date in qliksense

But, have tried this:

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

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

If it doesn't help, search the postgresql for help with the exact syntax.