Extract dates from textfield

[#1]

I have a problem.

You can see in the attachment the field interventions_date.

I need to use these dates in my application, but this is a simple textfield in the database.

Anyone any ideas?

Kind regards,

Katleen

[MK_QSL]

attachment missing !

[SunilChauhan]

could you share text file

[Colin-Albert]

There is an overview of the date functions here

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

There is a  detailed document on date functions here http://community.qlik.com/docs/DOC-3102

Hopefully these will help.

[MK_QSL]

Dont understand what you want to get ! kindly elaborate little more..

[#6]

I need all those seperate dates.

But they are in a textfield, so I don't know how to extract/convert them from that textfield?

[SunilChauhan]

open new qv desktaop-> edit script-> table files-> select Text file-> delimiter as tab

click finish. it will show you script like below

LOAD @1,

     @2,

     @3,

     @4,

     @5

FROM

\\delfiler3.fm.rbsgrp.net\chauhse\MyGEOSProfile\FDR\Desktop\test.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

load identify date field @ front end and rename it as Date in script

hope this helps

[MK_QSL]

Instead of interventions_date in the script, use below ...

Date(Date#(SubField(interventions_date,':'),'YYYY-MM-DD')) as interventions_date,

[#9]

Hi Katleen,

Will the number of dates always be the same amount?

[flipside]

You can iterate over a single field to pull out delimited values, however I note from your screenshot you have commas but that in row 17 (approx) has the comma missing. Something like this might work (basically read the code as three blocks working from the bottom block up)...

//3: Preceding load to handle space-delimited strings, splitting into separate rows
Load
sourceRow,
name,
subfield(trim(intervention_dates),' ',iterno()) as intervention_dates,
from_date,
to_date
while iterno()<=SubStringCount(trim(intervention_dates),' ')+1;

//2: Preceding load to handle comma-delimited strings, splitting into separate rows
Load
sourceRow,
name,
subfield(intervention_dates,',',iterno()) as intervention_dates,
from_date,
to_date
while iterno()<=SubStringCount(intervention_dates,',')+1;

//1: Data being loaded
Load *, recno() as sourceRow inline [
name, intervention_dates, from_date, to_date
GFT Route Donderdag, "2014-01-02, 2014-01-16, 2014-01-30", 2014-01-03, 2014-12-31
GFT Route Donderdag, "2015-01-02, 2015-01-16, 2015-01-30", 2015-01-03, 2015-12-31
GFT Route Donderdag, "2016-01-02, 2016-01-16 2016-01-30", 2016-01-03, 2016-12-31
];

flipside