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: 
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.