Date from Sql Select converting to Integer, but I want to keep it as a Date
I am using Qlik Sense - November 2017.
I am creating a Temp table by pulling data in from a SQL Select statement. I'm grabbing in the following fields:
In the SQL table, the [EndDate] is stored as a primary key with a date format.
I am formatting the Effective & End Dates as Date([Effective Date],'M/D/YYYY') & Date([End Date],'M/D/YYYY').
When I open the Data Model Viewer, the tags for Effective Date are ('numeric',integer','timestamp','date'). For End Date they are just ('numeric','integer') but is displayed as the 'M/D/YYYY' format that I defined.
The issue that this is creating is when I use these fields with the iterno() function to generate a record for ALL dates between the Effective and End Dates, I only get the value prior to the [Effective Date]
For the example below, here is the formula for the [Date] field:
Re: Date from Sql Select converting to Integer, but I want to keep it as a Date
Qlik does not have a strict notion of a data type tied to a field. Any value of a field can be any data type.
Field values that are interpreted as date (or variants thereof) will be so until they are operated upon by non-date aware functions or operators. So even Min() and Max() will turn a "proper" date into a date that is purely a numeric date and have to be "reformated" as a date after any operation.
If you add or substract constants or IterNo() or use Min() and Max() you should always wrap the entire operation with a Date() function to reformat or keep the format.
I have experienced that dates that certainly look like dates in Excel might not be interpreted as dates when brought into Qlik. The CrossTable prefix for the load statement will not stick to a date format when used in a load script. The CrossTable load will turn them into serial dates (numeric).
'Primary Key' in a SQL table has no significance or any particular meaning for Qlik when the column is imported as a field.
The most reliable fix is to always wrap expressions by using the Date() function. You don't have to supply the format string if it is the same as you have in your environment variables in the beginning of the load script.
Here are some useful links to enlightenment authored by Henric Cronström - they are all totally relevant for Qlik Sense although they have been written with QlikView in mind: