Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prees959
Creator II
Creator II

Extract and Format Date from Filename

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Then , try like:

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

View solution in original post

8 Replies
tresesco
MVP
MVP

What is the output you get for:

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


Could you share a sample file name you are having?

prees959
Creator II
Creator II
Author

Hi,

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

jayshrinipurte
Partner - Creator
Partner - Creator

Hi Phil,


try this,


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


Regards,

Jayshri

tresesco
MVP
MVP

Then , try like:

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

olivierrobin
Specialist III
Specialist III

hello

instead of date(), use date#()

prees959
Creator II
Creator II
Author

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

jayshrinipurte
Partner - Creator
Partner - Creator

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
Creator II
Creator II
Author

Many thanks for your explanation! much appreciated!