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:
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';
[Account_ID_18_Digit__c] as [Account ID],
Date([EffectiveDate]) as [New Effective Date],
Date('12/31/2025') as [EndDate]
// Date([EndDate]) as [EndDate]
set transaction isolation level read uncommitted;
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()-----
[New Effective Date],
iterno() as day,
Date([New Effective Date] + iterno() - 1) as [Period]
Where Date([New Effective Date] + iterno() - 1) <= [EndDate];
Drop table MG_Vert_A;
I also took off the Date() formatting in the Where clause and it still didn't work.