Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Importing portion of file name as a date?

Hello,

I'm having trouble with my code as I'm trying to automate a file. 

What I'm trying to do is bring a portion of the file name in as a date.  It doesn't appear to work.

My file is named Email by Client - Feb.xls

My logic seems sound.  I am taking the FileBaseName, searching the string for only the 3 characters from the right that I’ve named to abbreviate the month, turning that month into a numeric value, and then trying to turn it into a date.

If I use just (right(FileBaseName(),3) the word “Feb” comes in correctly.  It’s when I add num and date that don’t seem to transform it into a numeric date.  The reason I want to turn it into a numeric date is so that I can drop a bunch of files in this folder and add "Mar, May, June, etc" to their file names, essentially having a file for each month.

LOAD Department,
Total,
[Under 24],
Percentage,
date(num(right(FileBaseName(),3))) as Month
FROM

(
biff, embedded labels, table is [Worksheet$]);

1 Solution

Accepted Solutions
Vegar
Valued Contributor

Re: Importing portion of file name as a date?

You should tell QV that the 'Feb' is to be intepreted as a date using date#()

date(date#(right(FileBaseName(),3),'MMM')) as Month


Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Please ekskuse my Norglish and Swenglish typos.
2 Replies
Vegar
Valued Contributor

Re: Importing portion of file name as a date?

You should tell QV that the 'Feb' is to be intepreted as a date using date#()

date(date#(right(FileBaseName(),3),'MMM')) as Month


Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Please ekskuse my Norglish and Swenglish typos.
Not applicable

Re: Importing portion of file name as a date?

Worked like a charm!  Can't believe it was so simple.  Thank you!

Community Browser