Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Extract multiple dates per string

Hi all,

I have a field that can have multiple lines of text as it list the history of that field.

For example, one of my fields has the text below in ONE cell:

"1/19/2017 11:25 AM 01/19/17- No updates

1/3/2017 9:50 AM 01/03/17- Attempted to validate

12/22/2016 11:46 AM attempted to validate

12/13/2016 6:01 AM 12/13/16- Attempted to validate

11/4/2016 10:50 AM License End Date: 30-DEC-2016"

When I upload to QlikSense via the load editor, I want to take the first date that appears on the first line of the cell. I tried Date#() etc. but I can't make it work. The rest of teh cell should be ignored.

How can I extract 1/19/2017 from the first line above?

Reagrds,

Simon

2 Replies
Highlighted
Creator
Creator

Try this

LOAD *,SubField(field,' ',1) as date INLINE [

    field

    1/19/2017 11:25 AM 01/19/17- No updates

    1/3/2017 9:50 AM 01/03/17- Attempted to validate

    12/22/2016 11:46 AM attempted to validate

    12/13/2016 6:01 AM 12/13/16- Attempted to validate

    '11/4/2016 10:50 AM License End Date: 30-DEC-2016"'

];

Capture.PNG

Highlighted
Champion III
Champion III

Or you can use textbeteewn():

=date(TextBetween(FieldName, '', ' '))