topic Re: csv import and date/time conversions with WWW in QlikView

csv import and date/time conversions with WWW

angmarceau1971 — Wed, 25 Nov 2020 16:16:04 GMT

I am new to Qlikview and I'm struggling with a .csv import and date/time conversions.

My string field value: Mon, August 31, 2020 01:15:20.000

I've tried the following without success:

=date(timestamp#('MyDateTimeStampField', 'WWW, MMM DD, YYYY hh:mm:ss[.fff]'), 'YYYY/MM/DD')

=date(timestamp#('MyDateTimeStampField', 'WWW, MMM DD, YYYY hh:mm:ss.fff'), 'YYYY/MM/DD')

=date(timestamp#('MyDateTimeStampField', 'WWW, MMM DD, YYYY h:mm:ss.fff'), 'YYYY/MM/DD')

Can someone help me with what I'm doing wrong?

Re: csv import and date/time conversions with WWW

jwjackso — Thu, 03 Sep 2020 13:01:20 GMT

MMM is the format code for the short month name, Aug. Try MMMM to get August.

Re: csv import and date/time conversions with WWW

angmarceau1971 — Thu, 03 Sep 2020 14:22:48 GMT

It's not the MMM DD, YYYY hh:mm:ss.fff I'm having a problem with. That works if I remove the "Mon,", It seems to be the WWW part that is giving me issues.

Re: csv import and date/time conversions with WWW

angmarceau1971 — Thu, 03 Sep 2020 15:25:28 GMT

To Clarify:

This Works:

=date(timestamp#('August 31, 2020 01:15:20.000','MMM DD, YYYY hh:mm:ss.fff'), 'YYYY/MM/DD')

This does not Work and what I'm trying to get to work:

=date(timestamp#('Mon, August 31, 2020 01:15:20.000', 'WWW, MMM DD, YYYY hh:mm:ss.fff'), 'YYYY/MM/DD')

also tried this and doesn't work.

=date(timestamp#('Mon, August 31, 2020 00:00:00.000','WWW, MMMM DD, YYYY hh:mm:ss.fff'), 'YYYY/MM/DD')

Re: csv import and date/time conversions with WWW

angmarceau1971 — Tue, 08 Sep 2020 16:30:35 GMT

Any help on this from anyone? going on day 5 of trying to figure this out 😞

Re: csv import and date/time conversions with WWW

Brett_Bleess — Wed, 16 Sep 2020 15:53:29 GMT

Would recommend reviewing the following Help link as to what is actually supported with the function and the formatting required. I suspect the commas are the issue from what I can tell.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/conventions-number-time-formats.htm

Regards,
Brett