Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In this case try it with something like this: date(num#('42736', '#####'), 'DD.MM.YYYY')
- Marcus
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
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
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
Dear Marcus,
nope. Unfortunetaly, that's not it. Or can you convert the date with the file I've uploaded?
Cheers,
Ingo
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
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.
In this case try it with something like this: date(num#('42736', '#####'), 'DD.MM.YYYY')
- Marcus
Hi Marcus,
that did the trick! Many thanks!
Cheer,
Ingo