topic Re: QlikSense partially failing to load dates in App Development

QlikSense partially failing to load dates

jorge1 — Thu, 03 Nov 2022 16:47:41 GMT

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

Re: QlikSense partially failing to load dates

vinieme12 — Thu, 03 Nov 2022 17:19:28 GMT

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 ??

Re: QlikSense partially failing to load dates

jorge1 — Fri, 04 Nov 2022 08:23:32 GMT

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

Re: QlikSense partially failing to load dates

vinieme12 — Tue, 08 Nov 2022 01:33:59 GMT

can you post a sample?

Re: QlikSense partially failing to load dates

vinieme12 — Tue, 08 Nov 2022 02:07:16 GMT

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]

Re: QlikSense partially failing to load dates

jorge1 — Mon, 14 Nov 2022 14:56:31 GMT

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.