Convert a bad string to a date

[#1]

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.

[jagannalla]

Can you tell how the data looks of field baddate1. If the data of the field looks like below eg every time.

For eg:

baddate1

sum of  February 2012 /Bud.

sum of March 2012 /Bud.

[jagannalla · Accepted solution]

If this in the case. Use this code,

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

[#4]

Jagan,

That works a treat.

I was trying to avoid using Mid and Subfield, but it looks like I have to.

Thanks again, your help is much appreciated.

Tony

[jagannalla]

If it real helps you, Can you mark as helpfull answer for my post.

[#6]

Jagan,

(I accidentally flagged my own response as helpfull, I thought I was

flagging you as helpful)

I will happily flag your response as helpfull if I can find the option.

Thanks

Tony

[#7]

Hello, I am total beginner, and had very small knowledge of script functions. I am trying to understand what

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

does.

I thik I had simillar problem as Brian, i have date in format 1.13, 2.13,...10.13, 11.13. (it is the only date column in my table with over 75000 rows)

I wont be able to make more difficult calculations (while QV do not recognize my date field).

How can I make QV to understand this date format?  or can I make QV to calculate new dimensions like Month and Year from my "mesiac_rok" = month_year column?

[#8]

Start by reading this:

Date and Date# are conversion. First he determins what format he has, in this case MMM YYYY and this he wants to convert to DD/MM/YYYY.

You also have to be aware of what format your qlikview is in. You can changes these formats in the main script where SET format is written. - Create a new post where you need help, and attach a sample, then it is easier for people to respond, and then we dont spam this topic which is already answered.

Edit: sorry my browser went crazy

here it is:

http://community.qlik.com/servlet/JiveServlet/previewBody/3102-102-4-4191/DateFields-TechBrief-US_v2...

[#9]

Thank you very much!

I think, you will hear about me soon