topic Re: Get the text from a wildmatch expression in QlikView

Get the text from a wildmatch expression

ckmchinmaya — Thu, 25 Apr 2019 10:15:15 GMT

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

Re: Get the text from a wildmatch expression

sunny_talwar — Thu, 25 Apr 2019 11:54:54 GMT

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'))

Re: Get the text from a wildmatch expression

ckmchinmaya — Thu, 25 Apr 2019 12:08:39 GMT

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.

Re: Get the text from a wildmatch expression

sunny_talwar — Thu, 25 Apr 2019 12:14:30 GMT

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
];

Re: Get the text from a wildmatch expression

ckmchinmaya — Thu, 25 Apr 2019 12:54:54 GMT

Thanks a lot Sunny, this is working as expected 🙂