Extract date from string

[#1]

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:

1. VCI_KHT350_20090916_MB_Trp_011.dvl
2. VDR_TokyoXB30_20100917_072.dvl
3. 200900903-160735-sea-sea-xb30-ji-as-001.dvl
4. XAM151_20090827_Tp_027.dvl
5. VCI_KHT350_TRP_20070901_CB_013.dvl
6. VCI_KHT350_test_20090831_CB_001.dvl
7. 20090827-180259-sfo-sfo-xb30-tm-tf-001.dvl
8. 20090812-174003-msy-msy-xb30-as-cn001.dvl
9. dph_gfy607_20090724_DA_LDW022.dvl
10. VCI_20080331_DFB725_WT_004.dvl
11. 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.

[biester]

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

[#3 · Accepted solution]

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.

[biester]

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

[johnw]

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

[#6]

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.

[amien]

its really slow 😞 anyone got a fast one?