Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

Which database are you pulling this information from?

Not applicable
Author

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.

Anonymous
Not applicable
Author

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
Author

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

Anonymous
Not applicable
Author

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.