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

[WimDonckers]

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.

[marcus_sommer]

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

[MarcoWedel]

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

[WimDonckers]

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 ? 

[BrunPierre · Accepted solution]

Remove all the full stop as such.

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

[WimDonckers]

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

[marcus_sommer]

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