topic Re: Date Format in QlikView

Date Format

apoorvasd — Tue, 23 Aug 2016 10:22:46 GMT

Hello everyone,

I am pulling a date field from text file into QlikView. The values in date field in the text file are as shown in the attached picture. I am using the below expression to get the format of the date that i want to have.

=Date(Date#(Mid([Date],5,6) & ' ' & Right([Date],4) & ' ' & Mid([Date],12,8), 'MMM DD YYYY hh:mm:ss'),' DD-MM-YYYY hh:mm:ss')

Using this Expression I am getting the values like, 22-07-2016 12:36:25 which is what i want. But now the problem is, the dates which have single digit day value (like 1-08-2016, 2-08-2016 etc) are not being pulled into qlikview.

Note: In the text file, date field with single digit day value is as follows - Tue Aug 2 05:09:50 BST 2016.

If i change this value to Tue Aug 02 05:09:50 BST 2016 in the text file, then I see the data in QlikView.

So, is there way to pull these data into QlikView or should I change the data in text file itself?

Thank you

Re: Date Format

sunny_talwar — Tue, 23 Aug 2016 10:37:04 GMT

May be try this:

Table:

LOAD *,

TimeStamp(MakeDate(SubField(Date, ' ', -1), Month(Date#(SubField(Date, ' ', 2), 'MMM')), SubField(Date, ' ', 3)) + Time#(SubField(Date, ' ', 4), 'hh:mm:ss')) as DateTime;

LOAD * Inline [

Date

Fri Aug 5 00:11:36 BST 2016

Fri Aug 5 00:26:38 BST 2016

];

Re: Date Format

apoorvasd — Tue, 23 Aug 2016 11:08:05 GMT

Perfect! Thank you very much

Re: Date Format

sunny_talwar — Tue, 23 Aug 2016 11:46:55 GMT

No problem