Windows date system coming from xlsx files not convertible

[ingoniclas]

Hi all,

I load crosstables from xlsx files in QlikView 12.20 which have dates as headers. After having executed the crosstable load I tried to use 

Date(Date#(shipdate, 'DD.MM.YYYY'))

in order to convert Windows' date system, but this resulted in Null values. Interestingly, with old fashioned xls files this works just fine. But I couldn't find a way to convert this coming from xlsx files. I even created a reference table to convert the Windows date system through mapping, but this didn't work either. Any ideas?

Thanks,

Ingo

[Brett_Bleess]

Ingo, cannot recall having heard of this one elsewhere, so hopefully it is just a matter of getting the correct syntax on the conversion, it can be a bit crazy at times to get things right.  One thing that would help on this one is if you can attach an xlsx file, so we could mess with things directly to see if we can figure it out that way, but here are some links to check that may give you some further ideas:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

Those were the two that seemed to have the most likely impact, there are other date related posts in that area though, so if you want to search around yourself, use the following link for that:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[ingoniclas]

Hi Brett,

many thanks for your kind reply and the links. However, I have already read all those articles, that's why I posted this issue here. I have attached a sample of one of the xlsx files that cause the described issue. I haven't found a solution for this yet... 😞 I'd appreciate any ideas on how to tackle this issue.

Best regards,

Ingo

[marcus_sommer]

I assume after loading the file with a crosstable-statement you get the values formatted like: Jan 17 and not just 01.01.2017 and therefore your converting-pattern doesn't fit. Instead of 'DD.MM.YYYY' try it with:

Date(Date#(shipdate, 'MMM YY'))

- Marcus

[ingoniclas]

Dear Marcus,

nope. Unfortunetaly, that's not it. Or can you convert the date with the file I've uploaded?

Cheers,

Ingo

[marcus_sommer]

How does shipdate look like without any converting/formatting? Especially keep an eye on possible leading/following/inbetween spaces of the values respectively if there are other invisible chars, too. The above mentioned converting worked only if the format-pattern fits exactly.

- Marcus

[ingoniclas]

Hi Marcus,

I work with what I get from the client, and it looks like the snapshot below. The format is a custom date with 'MMM YY' (German: 'Benutzerdefiniert').  When I load it like this in QV it displays the Windows time format (e.g. 42736). So it is actually recognized as a date you might think. However, any attempt to convert it into s.th. like 01.01.2019 did not work and only resulted in NULL-values!? I'm puzzled.

[marcus_sommer · Accepted solution]

In this case try it with something like this: date(num#('42736', '#####'), 'DD.MM.YYYY')

- Marcus

[ingoniclas]

Hi Marcus,

that did the trick! Many thanks!

Cheer,

Ingo