Extracting date and not text

[bobbydave]

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.?

[Chanty4u]

try this in ur script

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

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

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

[woshua5550]

I think this would work

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

[bobbydave]

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

[Anil_Babu_Samineni]

This should work until unless Different string like

CPS_TIA_V_Off_Sys_Figs_LATAM_201606_ABC.xls

[bobbydave]

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