topic Re: Extract dates from textfield in QlikView

Extract dates from textfield

Mon, 28 Jul 2014 08:51:55 GMT

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

Re: Extract dates from textfield

MK_QSL — Mon, 28 Jul 2014 08:52:52 GMT

attachment missing !

Re: Extract dates from textfield

SunilChauhan — Mon, 28 Jul 2014 08:53:47 GMT

could you share text file

Re: Extract dates from textfield

Colin-Albert — Mon, 28 Jul 2014 09:01:22 GMT

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.

Re: Extract dates from textfield

MK_QSL — Mon, 28 Jul 2014 09:18:08 GMT

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

Re: Extract dates from textfield

Mon, 28 Jul 2014 09:32:07 GMT

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?

Re: Extract dates from textfield

SunilChauhan — Mon, 28 Jul 2014 09:38:02 GMT

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,

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

Re: Extract dates from textfield

MK_QSL — Mon, 28 Jul 2014 09:41:07 GMT

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

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

Re: Extract dates from textfield

Mon, 28 Jul 2014 09:53:55 GMT

Hi Katleen,

Will the number of dates always be the same amount?

Re: Extract dates from textfield

flipside — Mon, 28 Jul 2014 10:03:49 GMT

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

Re: Extract dates from textfield

Mon, 28 Jul 2014 11:36:15 GMT

Still testing alle the possible suggestions.

If it would be helpful, I will attach the qvd file with the data

Re: Extract dates from textfield

Mon, 28 Jul 2014 11:44:02 GMT

How do I do this from a qvd?

Re: Extract dates from textfield

flipside — Mon, 28 Jul 2014 12:01:36 GMT

You just need to replace the 1st section to something like ...

//1: Data being loaded

LOAD name,
     intervention_dates,
     from_date,
     to_date,
     recno() as sourceRow
FROM
[???.qvd] (qvd);

flipside

Re: Re: Extract dates from textfield

Mon, 28 Jul 2014 12:16:33 GMT

Unfortunatly the result doesn't seem correct (see attachment)

Re: Extract dates from textfield

flipside — Mon, 28 Jul 2014 12:51:48 GMT

Try this using the qvd you uploaded ...

//3: Preceding load to handle space-delimited strings, splitting into separate rows (there's only one row in the qvd with this issue). As it is the last step, I also set the date field to date format.
Load
id,
sourceRow,
name,
date(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
id,
sourceRow,
name,
trim(subfield(intervention_dates,',',iterno())) as intervention_dates,
from_date,
to_date
while iterno()<=SubStringCount(intervention_dates,',')+1;

//1: Data being loaded
LOAD id,
     name,
     intervention_dates,
     from_date,
     to_date,
     recno() as sourceRow
FROM
[Collection_routes.qvd] (qvd);

I get 1715 rows total.

flipside

Re: Re: Extract dates from textfield

Mon, 28 Jul 2014 13:00:19 GMT

Looks great!

Thank you very much for your help!

Kind regards,

Katleen

Re: Extract dates from textfield

flipside — Mon, 28 Jul 2014 13:10:04 GMT

Great.

The only caveat to this is that with the source being a text string, if another delimiter was introduced, or a bad date was encountered, it would display incorrectly. Preferably these should be prevented from being entered at source, but if this cannot be done then you would need to add further 'preceding loads' to handle them.

flipside