topic Re: extract a date from a free text field in QlikView

extract a date from a free text field

Wed, 10 Jun 2015 14:30:09 GMT

Hi experts!

I have an 'Update' field loaded in from a SharePoint site that I can't alter, that is a text update. The users always start their update with the date, then text explaining why the action is late! I want to be able to show how long since the last update, so I'm looking for a way to extract the first 10 characters of the text (which include the date) and make that into a date field that I can use.

So for example, the 'Update' field might contain:

12/10/2014 - Projects are proposed. A scheme paper for the first of these is ready for review

I'd like to create a date field called 'LastUpdated' from the 12/10/2014

Any suggestions?

Re: extract a date from a free text field

ramoncova06 — Wed, 10 Jun 2015 14:40:10 GMT

if you are sure that the date is always at the start you can use date(left(text,10))

Re: extract a date from a free text field

fvelascog72 — Wed, 10 Jun 2015 14:40:38 GMT

Hi,

Try this:

left([Update],10) as "LastUpdated"

Re: extract a date from a free text field

swuehl — Wed, 10 Jun 2015 14:41:14 GMT

LOAD

Date#(Left(Textfield,10),'MM/DD/YYYY') as LastUpdated,

Textfield,

...

FROM ...;

Re: extract a date from a free text field

Wed, 10 Jun 2015 14:41:47 GMT

Hi,

You can use:

LOAD Update,

DATE#(LEFT(Update,10),'MM/DD/YYYY') AS Date

RESIDENT A;

Or you can find attached sample

Hope helps

Regards,

David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: www.davidshuang.com

Re: extract a date from a free text field

Wed, 10 Jun 2015 15:04:14 GMT

Thanks David, that was easier than I thought - but then it always is after someone has shown you the way!

Now I've just got to make sure the users keep entering the dates the same!

Re: extract a date from a free text field

swuehl — Wed, 10 Jun 2015 15:06:10 GMT

Look into the HELP, alt() function and its example on how to parse differently formatted dates in.