Date Format

[apoorvasd]

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

[sunny_talwar · Accepted solution]

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

];

[apoorvasd]

Perfect! Thank you very much

[sunny_talwar]

No problem