topic Re: Convert a bad string to a date in QlikView

Convert a bad string to a date

Thu, 19 Apr 2012 19:19:20 GMT

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.

Convert a bad string to a date

Thu, 19 Apr 2012 19:34:46 GMT

Can u upload the data file by chance??

Convert a bad string to a date

Thu, 19 Apr 2012 20:04:43 GMT

try with

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

Convert a bad string to a date

Thu, 19 Apr 2012 20:33:31 GMT

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

Convert a bad string to a date

Thu, 19 Apr 2012 21:28:25 GMT

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

rwunderlich — Fri, 20 Apr 2012 00:53:34 GMT

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

Convert a bad string to a date

adhudson — Fri, 20 Apr 2012 00:56:36 GMT

Hi,

Yes. I agree with Rob.

It is working perfectly without any use of string functions.

Regards

Andrew Hudson

Convert a bad string to a date

Sun, 22 Apr 2012 08:35:26 GMT

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

rwunderlich — Sun, 22 Apr 2012 16:24:14 GMT

Tony, can you provide a few sample lines?

-Rob

Re: Convert a bad string to a date

Wed, 25 Apr 2012 11:48:04 GMT

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

Re: Convert a bad string to a date

jagannalla — Wed, 25 Apr 2012 12:01:55 GMT

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.

Re: Convert a bad string to a date

jagannalla — Wed, 25 Apr 2012 12:50:39 GMT

If this in the case. Use this code,

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

Re: Convert a bad string to a date

Wed, 25 Apr 2012 13:54:17 GMT

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

Re: Convert a bad string to a date

jagannalla — Wed, 25 Apr 2012 13:58:12 GMT

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

Re: Convert a bad string to a date

Wed, 25 Apr 2012 14:32:23 GMT

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

Re: Convert a bad string to a date

Wed, 02 Jul 2014 09:28:23 GMT

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?

Re: Convert a bad string to a date

Wed, 02 Jul 2014 09:33:14 GMT

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.pdf

Re: Convert a bad string to a date

Wed, 02 Jul 2014 09:49:23 GMT

Thank you very much!

I think, you will hear about me soon