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: 
kvijay173
Creator
Creator

Date is in text format. How to convert it?

My date column name is "MyDate" and the data is in text format as

Thursday,   July 31, 2014 04:04:54 PM

QlikView 11 is not identifying this as date. How to extract date and time from this kind of data?

13 Replies
mohammadkhatimi
Partner
Partner

Hie Vijay,

Date(Date#(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',2)&Left(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',3),5),' MMMM DD YYYY'))

Hope this will help you,

Regards,

Mohammad

mohammadkhatimi
Partner
Partner

Hie Vijay,

Date(Date#(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',2)&Left(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',3),5),' MMMM DD YYYY'))

Hope this will help you,

Regards,

Mohammad

kvijay173
Creator
Creator
Author

Thank you all for your efforts. Finally worked with

Date( Date#(Trim(TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#')) , 'MMMM DD, YYYY hh:mm:ss TT'))

Still breaking my head to understand this solution. But I have got the result

anbu1984
Master III
Master III

From QV manual,


TextBetween(s , beforetext , aftertext [, n ])

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>') returns 'abc

>TextBetween('<abc><de>', '<', '>',2) returns 'de'

TextBetween() returns string between 'day,' and '#'. Added # to date field which will be aftertext


TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#') -- Returns 'July 31, 2014 04:04:54 PM'