Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Convert a bad string to a date

I have a field that I'm trying to extract a date from.

It's in the format, text February 2012 text.

Using the functions subfield and mid I now have February 2012.

How can I convert this to a date field, e.g. 01/02/2012

Regards

Tony

Message was edited by: QlikQuest Thanks for the response. I'm not at the office but I've created  a similiar scenario. Thanks again.

1 Solution

Accepted Solutions
jagannalla
Valued Contributor III

Re: Convert a bad string to a date

If this in the case. Use this code,

Date(Date#(Trim(SubField(mid(baddate1,7),'/')),'MMM YYYY'),'DD/MM/YYYY') as DateField

17 Replies
Not applicable

Convert a bad string to a date

Can u upload the data file by chance??

Not applicable

Convert a bad string to a date

try with

date(yourdate#('01/' & left(yourdate,3) & '/' & right(yourdate,4),'DD/MMM/YYYY')) as newdate

Not applicable

Convert a bad string to a date

whoops just noticed I made a mistake.

try with

load date(date#('01/' & left([Bad Date],3) & '/' & right([Bad Date],4),'DD/MMM/YYYY')) as newdate

Not applicable

Convert a bad string to a date

Juan,

You are a top guy.

I've amended your suggestion to include the mid function to strip out the bad text and it works a treat.

date(date#('01/' & left(mid(baddate,3),3) & '/' & right(mid(baddate,3),4),'DD/MMM/YYYY')) as newdate,

Many Many thanks.

Tony

Convert a bad string to a date

I don't think there is any need to substring the baddate. You should be able to read it directly with Date#() by supplying the mask.

date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')

-Rob

http://robwunderlich.com

adhudson
Contributor II

Convert a bad string to a date

Hi,

     Yes. I agree with Rob.

     It is working perfectly without any use of string functions.

Regards

Andrew Hudson

Not applicable

Convert a bad string to a date

Rob, Andrew,

Thanks for your reply.

When I your suggestion,  date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')

The script executes OK but no data is returned.

If you could offer any suggestions I'd appreciate it.

Thanks

Tony

Convert a bad string to a date

Tony, can you provide a few sample lines?

-Rob

Not applicable

Re: Convert a bad string to a date

Rob,

Thanks for the reply.

I've attached a simple spreadsheet with the type of string supplied from the user and

a QV doc applying your suggestion.

However no data is returned when I apply date(date# etc.

I really would like not to have to substring the field as per your suggestion.

Thanks

Tony

Community Browser