Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
juleshartley
Specialist
Specialist

Strange date result SQL script

Hi all,

I have a very strange issue! We are loading some data using a simple sql script from a view:

ie. select * from AView

There is a field in the view called 'datefeesreceived' that is 0 in the database for certain rows, but when it is viewed in the qvw is shows a date (and  I don't mean 30/12/1899 or similar but 24/6/2016!). 

If I add a 'where' clause to the sql statement so that it only returns the rows that I expect to have 0 value then they come back with 0! 

I cannot understand what is going on...

Thanks!

Labels (2)
1 Reply
Nicole-Smith

QlikView uses "duck typing" where it will treat the field like whatever it looks to be.  Since you have a combination of numbers and dates, it treats it like the date, but when it's only the number, it treats it like a number.  A single column will only be cast as one data type--so when you have a combination of multiple, it's going to choose one or the other, not both.  It's really poor design that there is a mixture of two data types in a single column in your SQL view--those zeroes really should be NULL values.  If you're unable to update the view itself, I would add a case statement to your SQL script to update those zeroes to NULLs, then everything should flow through properly.