topic AW:Extracting Dates/Time Slices from a String in QlikView

Extracting Dates/Time Slices from a String

Tue, 26 Apr 2011 16:16:12 GMT

Greetings everyone!

I have an issue to solve, but I just can't figure out how to do it...

I must extract a StartDate / FinalDate, and StartTime / EndTime from the following string:

''From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00''

Any ideas?

Thank you all one more time!

Extracting Dates/Time Slices from a String

matt_crowther — Tue, 26 Apr 2011 16:39:33 GMT

Could you post some sample data as this should be quite straight forward assuming the data is fairly uniform.

All the best,

Matt - Visual Analytics Ltd

AW:Extracting Dates/Time Slices from a String

brenner_martina — Tue, 26 Apr 2011 16:45:05 GMT

Hi,

there are the string functions: Left, Right and Mid, it could look like this:

Load ...,
Date(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',7,10),'D/M/Y') As DateStart,
Date(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',21,10),'D/M/Y') As DateEnd
Time(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',40,5),'hh:mm') As TimeStart,
Time(Right('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',5),'hh:mm') As TimeEnd,
...
From ... ;

Your only Problem is, that you have in Oct, Nov and dec 2 numbers for the month.

Extracting Dates/Time Slices from a String

Tue, 26 Apr 2011 16:47:16 GMT

Try this:

Startdate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 2),' ',1)

FinalDate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 3),' ',1)

StartTime: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 1),'en: ', 2)

EndTime: =subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 2)

Regards

Extracting Dates/Time Slices from a String

Tue, 26 Apr 2011 22:12:34 GMT

I would go...


date(mid([Field Name],index([Field Name],' ')+1,mid([Field Name],index([Field Name],' ',2)-1),'D/M/YYYY') as 'StartDate'

And do the same for the others except use ,3,4 in the index and so on. Probably messed up syntax somewhere or did the date 'D/M/YYYY' thing wrong but hopefully you get the idea.

Extracting Dates/Time Slices from a String

Wed, 27 Apr 2011 14:24:04 GMT

Thank you very much for all your answers and suggestions!

I have performed a workaround and treated the data in a different way, but all your suggestions worked fine! A combination of them form a perfect solution!

Again, thx for your attention!