topic Re: Extracting date and not text in QlikView

Extracting date and not text

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

SourceData:

first 1

LOAD

//subfield(FileName(),'CPS_',2) as XLS_SourceSB

left(right(subfield(FileName(),'CPS_',2),25),6) as XLS_SourceSB

FROM

[$(vDGD)\CPS_TIA_V_Off_Sys_Figs_$(vcurrentyear)*.xlsx]

(ooxml, embedded labels, table is [CPS_TIA_V_Off_Sys Figs]);

Filenames:

CPS_TIA_V_Off_Sys_Figs_201609.xls

CPS_TIA_V_Off_Sys_Figs_LATAM_201606.xls

I am trying to extract the dates from the above filename but i dont want the LATAM filename. How do I get around this in order to ensure that XLS_SourceSB takes in the YYYYMM (201609) and NOT LATAM.?

Re: Extracting date and not text

Chanty4u — Fri, 17 Nov 2017 10:43:06 GMT

try this in ur script

SubField(FileBaseName(),'_',7) as new,

Re: Extracting date and not text

Anil_Babu_Samineni — Fri, 17 Nov 2017 10:43:41 GMT

Perhaps this?

=Left(SubField(FieldName, '_', -1), 6)

Re: Extracting date and not text

woshua5550 — Fri, 17 Nov 2017 10:54:48 GMT

I think this would work

KeepChar(FileName(),'0123456789') as XLS_SourceSB

Re: Extracting date and not text

bobbydave — Fri, 17 Nov 2017 10:54:59 GMT

This will only take the first file name and not the filename containing LATAM?

Re: Extracting date and not text

Anil_Babu_Samineni — Fri, 17 Nov 2017 10:57:22 GMT

This should work until unless Different string like

CPS_TIA_V_Off_Sys_Figs_LATAM_201606_ABC.xls

Re: Extracting date and not text

bobbydave — Fri, 17 Nov 2017 11:01:15 GMT

Yes the LATAM filename will always be in the same format so this is perfect if it ignores it and will now only consider the other filename.

Thanks