QlikSense partially failing to load dates

[jorge1]

Good afternoon,

After breaking my head for a long time, I found the culprit of mismatches between the results I was obtaining in QlikSense and other application. The problem is that I have no clue on how to solve the issue.

I have a date field in my data set. QlikSense is only partially loading it. The dates are all formatted equally in the data source. QlikSense is only parsing some of them into values while returning null for the rest. A picture will show it better: 

Any support would be much appreciated

[vinieme12]

Classic case of garbage in garbage out 

There is absolutely nothing to do here 

 you may have dates for example  6/9/2022 how can you tell if it's June 9th or Sept 6th ??

[jorge1]

Is it though? I know for a fact that the date consistently comes in a DD/MM/YYYY format. Furthermore, I am instructing QlikSense to use this format when loading the data by having:

Date(Date#([DateField], 'DD/MM/YYYY') ) AS [DateField]

Neither Python nor PowerBI had any problem parsing these dates so either there is some knowledge I am missing about working with dates in QlikSense or this is a bug...

[vinieme12]

can you post a sample?

[vinieme12]

is the table a result of concatenation and the other table does not have the below formatting applied?

Date(Date#( TRIM([DateField]), 'DD/MM/YYYY') ) AS [DateField]

[jorge1 · Accepted solution]

Solved the issue. The root cause of the issue seems to be that Excel is somehow altering some of the dates and parsing them to the Excel date format (which visualizes as a date but stores as an integer).

This results in a null value when QlikSense tries to parse those Excel date formats.

I was not able to figure out what where the triggers for values to be parsed to dates in Excel though.