Get the text from a wildmatch expression

[ckmchinmaya]

Hi Guys,

I have a simple requirement but not able trace the right link for it. So posted here.

I am getting different file names which is having "Year" as part of the file name. I want to have some trend based on the "Quarter" & "Year" for which the file generated. I don't want to assume the "Year" is always in a fixed position especially from the right side.

My filename looks like : DK_Q3_XYX_DTRR_KL_2018-09-21_08-48-08.xlsx , to understand if the file name is holding  a valid "year" or not i am using the below expression  WildMatch(File,'*_20??-*'). At the moment we can assume, the filename always in YYYY format.

I want your suggestion what is the best way to extract the "Year" from the file name considering the dynamics ( specifically by the expression mentioned in the WildMatch function.

Thanks a lot

[sunny_talwar]

May be this to extract year

Year(Date#(SubField('DK_Q3_XYX_DTRR_KL_2018-09-21_08-48-08.xlsx', '_', 6), 'YYYY-MM-DD'))

or this to get Date

Date(Date#(SubField('DK_Q3_XYX_DTRR_KL_2018-09-21_08-48-08.xlsx', '_', 6), 'YYYY-MM-DD'))

[ckmchinmaya]

Hi Sunny,

Thanks for your response. The problem here is we can't assume the date position - it will change from file to file.

Some files it may come after 3/4 "_" blocks

SubField('DK_Q3_XYX_DTRR_KL_2018-09-21_08-48-08.xlsx', '_', 6)

 We may need to recognize the "Year" i.e. "YYYY" pattern some thing like '*_20??-??-??_*' in some way. 

[sunny_talwar · Accepted solution]

May be this

Table:
LOAD File,
     Date(Date#(20 & TextBetween('_'& SubField(File, '.', 1) & '_', '_20', '_'), 'YYYY-MM-DD')) as Date;
LOAD * INLINE [
    File
    DK_Q3_XYX_DTRR_KL_2018-09-21_08-48-08.xlsx
    DK_Q3_2018-09-21_XYX_DTRR_KL_08-48-08.xlsx
    DK_Q3_XYX_DTRR_KL_08-48-08_2018-09-21.xlsx
    2018-09-21_DK_Q3_XYX_DTRR_KL_08-48-08.xlsx
];

[ckmchinmaya]

Thanks a lot Sunny, this is working as expected 🙂