Date from Sql Select converting to Integer, but I want to keep it as a Date

[mikelutomski]

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:

Account ID,

Min([Effective Date]),

Max([End Date])

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:

[Effective Date] + iterno() - 1 where [Effective Date] + iterno() - 1 < = [End Date]

Account ID          Effective Date          End Date          Iterno()          Date

1                         7/1/2016                    12/31/2018     0                    6/30/2016

My guess is that this is NOT working because the [End Date] is being returned as numeric/integer.  Is this because it is a 'Primary Key' in the sql table?

Is there something I'm doing wrong with the iterno() function that is causing this to not work?

Thank you.

-Mike

[petter]

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:

Data Types in QlikView

Automatic Number Interpretation

The Date Function

Why don’t my dates work?

On Format Codes for Numbers and Dates

Dates in Set Analysis

[mikelutomski]

Thank you, Petter.

Even after putting the Date() around the fields, and even hard coding the [EndDate], I am still only getting 1 record.

Here is my logic:

LIB CONNECT TO 'SQLCHSADB';

MG_Vert_A:

Load

[Account_ID_18_Digit__c] as [Account ID],

Date([EffectiveDate]) as [New Effective Date],

Date('12/31/2025') as [EndDate]

//       Date([EndDate]) as [EndDate]

      ; 

SQL

set transaction isolation level read uncommitted;

SELECT Distinct

[Account_ID_18_Digit__c],

MIN([snpEffectiveDate]) [EffectiveDate],

MAX([snpExpirationDate]) [EndDate]

FROM [SFDC_Archive].[snap].[Account_Snapshot]

where Clarify_Site_ID__c IS NOT NULL

    and [Account_ID_18_Digit__c] = '001d000001aLcrEAAS'

group by [Account_ID_18_Digit__c]

order by [Account_ID_18_Digit__c]

;

//-----testing for iterno()-----

NoConcatenate

iterno:

Load

[Account ID],

    [New Effective Date],

    [EndDate],

    iterno() as day,

    Date([New Effective Date] + iterno() - 1) as [Period]

    Resident MG_Vert_A

    Where Date([New Effective Date] + iterno() - 1) <= [EndDate];

Drop table MG_Vert_A;

exit script;

I also took off the Date() formatting in the Where clause and it still didn't work.

Thanks.

-Mike