Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Extracting Dates/Time Slices from a String

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

5 Replies
matt_crowther
Luminary Alumni
Luminary Alumni

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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!