topic Date# does not recognize date field content (for some records) in App Development

Date# does not recognize date field content (for some records)

WimDonckers — Fri, 21 Oct 2022 13:53:44 GMT

When loading a date field I use the following script to transform it to a date field:

date#(Transactiedatum,'DD-MMM-YYYY') as Transactiedatum2

This works fine for most of the records but not all:

result:

attached is the sourcefile. I can't see what is wrong with those specific dates.

Any suggestion would be welcome.

Re: Date# does not recognize date field content (for some records)

marcus_sommer — Fri, 21 Oct 2022 15:36:12 GMT

The format doesn't probably match the set format-pattern within the interpretation-variables - usually at the beginning of the script. I could imagine that instead of 'okt' there is 'oct' specified ...

- Marcus

Re: Date# does not recognize date field content (for some records)

MarcoWedel — Fri, 21 Oct 2022 15:53:14 GMT

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInterpretationVariables/MonthNames.htm

Re: Date# does not recognize date field content (for some records)

WimDonckers — Fri, 21 Oct 2022 17:48:13 GMT

I had the following :

SET MonthNames='jan.;feb.;mrt.;apr.;mei.;jun.;jul.;aug.;sep.;okt.;nov.;dec.';

you notice the period after each month.

If I remove all those periods, then it works. But howcome it worked for the other months ?

Re: Date# does not recognize date field content (for some records)

BrunPierre — Fri, 21 Oct 2022 18:32:08 GMT

Remove all the full stop as such.

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

Re: Date# does not recognize date field content (for some records)

WimDonckers — Tue, 25 Oct 2022 07:36:46 GMT

Question remains why the problem only occurred for 'okt' and 'mrt'.

Re: Date# does not recognize date field content (for some records)

marcus_sommer — Tue, 01 Nov 2022 08:21:59 GMT

Thinkable are various causes which may impact these results, for example:

"hidden" chars like control chars or some kinds of white-space within the raw-data and/or within the format-pattern - sounds strange but I have already seen cases in which tools or copy & paste measures have added ones
collation, see: Search - But what shall you find? - Qlik Community - 1472194
priority respectively order of resolving converting measures - if interpretation-variables are set Qlik will take them and if none (valid ???) exists Qlik will look for the region-settings of the OS

- Marcus