Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ckmchinmaya
New Contributor III

Get the text from a wildmatch expression

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

1 Solution

Accepted Solutions

Re: Get the text from a wildmatch expression

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

Re: Get the text from a wildmatch expression

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
New Contributor III

Re: Get the text from a wildmatch expression

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

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
New Contributor III

Re: Get the text from a wildmatch expression

Thanks a lot Sunny, this is working as expected 🙂