topic Re: Extract and Format Date from Filename in QlikView

Extract and Format Date from Filename

prees959 — Wed, 23 May 2018 06:21:31 GMT

Hi,

I'm using the following expression to extract and format the date from a filename during the load process:

Date(left(subfield(subfield('$(File)','\', -1), '_', -1), 8),'YYYY_MM_DD');

Its extracting the date correctly but its not coming through in the right YYYY_MM_DD format. Instead Im getting this

57152_05_05 where the actual date should be 2018_05_23

Can anyone help on this?

Many thanks,

Phil

Re: Extract and Format Date from Filename

tresB — Wed, 23 May 2018 06:26:53 GMT

What is the output you get for:

left(subfield(subfield('$(File)','\', -1), '_', -1), 😎 ?

Could you share a sample file name you are having?

Re: Extract and Format Date from Filename

prees959 — Wed, 23 May 2018 06:35:13 GMT

Hi,

In the above example I get 20180523 by using left(subfield(subfield('$(File)','\', -1), '_', -1), 😎

Re: Extract and Format Date from Filename

jayshrinipurte — Wed, 23 May 2018 06:42:45 GMT

Hi Phil,

try this,

Date(Date#(left(subfield(subfield('$(File)','\', -1), '_', -1), 8),'YYYYMMDD'),'YYYY_MM_DD');

Regards,

Jayshri

Re: Extract and Format Date from Filename

tresB — Wed, 23 May 2018 06:42:55 GMT

Then , try like:

Date(Date#(left(subfield(subfield('$(File)','\', -1), '_', -1), 8), 'YYYYMMDD'), 'YYYY_MM_DD')

Re: Extract and Format Date from Filename

olivierrobin — Wed, 23 May 2018 06:50:49 GMT

hello

instead of date(), use date#()

Re: Extract and Format Date from Filename

prees959 — Wed, 23 May 2018 08:31:09 GMT

Thanks! Can you explain the why Date# and Date functions are required?

Re: Extract and Format Date from Filename

jayshrinipurte — Wed, 23 May 2018 08:37:59 GMT

Dear Phil,

Date#() function is called Interpretation function.It will convert the string -to-number format. Date() function is called Formatting function.It will convert number-to-string format.

Normally Qlikview itself as an intelligence to interpret the date function but at times it will failed to interpret the date correctly. During those times we need to use date#() function manually help the qlikview to interpret the date.

Date#('your-date-field','current date_format')

Once the Qlikview is recognized the correct format of date it will map the date to the corresponding number.From that you can use date() function to format the date to any form.

Date(Date#('your-date-field','current date_format'),'required date_format')

Regards,

Jayshri

Re: Extract and Format Date from Filename

prees959 — Wed, 23 May 2018 09:24:26 GMT

Many thanks for your explanation! much appreciated!