Extract and Format Date from Filename

[prees959]

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

[tresesco]

What is the output you get for:

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

Could you share a sample file name you are having?

[prees959]

Hi,

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

[jayshrinipurte]

Hi Phil,

try this,

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

Regards,

Jayshri

[tresesco · Accepted solution]

Then , try like:

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

[olivierrobin]

hello

instead of date(), use date#()

[prees959]

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

[jayshrinipurte]

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

[prees959]

Many thanks for your explanation! much appreciated!