topic Extract date from string in QlikView

Extract date from string

Fri, 02 Oct 2009 16:26:23 GMT

When loading data from an Excel file I'm trying to get the date (yyyymmdd) information from a column with different file names inte a separete field. The problem is that the file name doesn't have the same formate all the time and there could also be spelling errors or missing date information. I have the following exampel data from a column:

VCI_KHT350_20090916_MB_Trp_011.dvl
VDR_TokyoXB30_20100917_072.dvl
200900903-160735-sea-sea-xb30-ji-as-001.dvl
XAM151_20090827_Tp_027.dvl
VCI_KHT350_TRP_20070901_CB_013.dvl
VCI_KHT350_test_20090831_CB_001.dvl
20090827-180259-sfo-sfo-xb30-tm-tf-001.dvl
20090812-174003-msy-msy-xb30-as-cn001.dvl
dph_gfy607_20090724_DA_LDW022.dvl
VCI_20080331_DFB725_WT_004.dvl
VCI_DFB725_30d2_em2_AK_200.dvl

#3 have a spelling error in the file name
#11 have no date information

Both #3 and #11 should be handeld as if date is unkown.

I've tried with wildmatch(), index(), mid() and subfield() and i guess they should be used with a for loop (or IF), but I can't get it to work.

Extract date from string

biester — Fri, 02 Oct 2009 16:45:56 GMT

The appropriate tool for this is a Regular Expression, but as far as I know they are not supported by QlikView. But you could write a VBScript function in the module using RegExp Object and use it in the script. That's what I would do.

Rgds,
Joachim

Extract date from string

Fri, 02 Oct 2009 22:25:58 GMT

just by analyzing the example you posted of your information, this worked:


test:
load
makedate(left(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),4),mid(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),5,2),right(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),2)) as Date,
left(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),4) as Year,
mid(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),5,2) as Month,
right(if(index(value,'20')>0 and (mid(value,index(value,'20')+8,1)='-' or mid(value,index(value,'20')+8,1)='_'),mid(value,index(value,'20'),8)),2) as Day
inline [
value
VCI_KHT350_20090916_MB_Trp_011.dvl
VDR_TokyoXB30_20100917_072.dvl
20090903-160735-sea-sea-xb30-ji-as-001.dvl
XAM151_20090827_Tp_027.dvl
VCI_KHT350_TRP_20070901_CB_013.dvl
VCI_KHT350_test_20090831_CB_001.dvl
20090827-180259-sfo-sfo-xb30-tm-tf-001.dvl
20090812-174003-msy-msy-xb30-as-cn001.dvl
dph_gfy607_20090724_DA_LDW022.dvl
VCI_20080331_DFB725_WT_004.dvl
VCI_DFB725_30d2_em2_AK_200.dvl
];

you could add more checks to make sure the loaded date is in reality a date, as I only check for '20' and a "_" or a "-" 8 chars later.

Extract date from string

biester — Fri, 02 Oct 2009 23:22:12 GMT

Just to demonstrate the power of regular expressions see enclosed my sample. You can add filenames in whatever shape you like as long as there is a date in the format YYYYMMDD included (beginning with year 20.., but you can modify the Regular Expression used)

QlikView should really add RegEx support to it's features .... I'll launch a feature request the next days.

Rgds,
Joachim

Extract date from string

johnw — Fri, 02 Oct 2009 23:29:40 GMT

Yeah, regular expression support seems like a pretty basic feature. Hopefully it will get added at some point.

Extract date from string

Mon, 05 Oct 2009 14:22:12 GMT

I worked, thank you. But I will definitely recommend my customer to do changes in the database and add more fields where date and other information from the string are divided into separate fields.

Extract date from string

amien — Tue, 16 Feb 2010 17:47:20 GMT

its really slow 😞 anyone got a fast one?