Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ingoniclas
Creator II
Creator II

Windows date system coming from xlsx files not convertible

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

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

View solution in original post

8 Replies
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
ingoniclas
Creator II
Creator II
Author

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
MVP & Luminary
MVP & Luminary

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
Creator II
Creator II
Author

Dear Marcus,

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

Cheers,

Ingo

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Creator II
Creator II
Author

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.

Custom.JPG

marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

View solution in original post

ingoniclas
Creator II
Creator II
Author

Hi Marcus,

that did the trick! Many thanks!

Cheer,

Ingo