Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ngreddy1982
Contributor III
Contributor III

FileBaseName to _DateKey

Hi all, 

I have an Excel file to load into QVD, in which I need to create a date key based on the file base name. 

My excel file name is like YYYY-MM-DD_Country_Currency, need to convert the first 10 digits to YYYYMMDD

I have tried the below

Num(Subfield(FileBaseName(),'-',1) & num(subfield(FileBaseName(),'-',2),'00') & num(subfield(SubField(FileBaseName(),'_',1),'-',3),'00')) AS _DateKey, but it is converting into text in qvd but not as numeric format. 

Thanks in advance. 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Keepchar() is a string-function and therefore is the return a string but usually are only numeric returns interpreted as numbers. Nevertheless you could force a numeric interpretation with something like:

num(num#(keepchar(filebasename(), '0123456789')), '########')

- Marcus

 

View solution in original post

6 Replies
stevejoyce
Creator III
Creator III

How about trying makedate() function.

Gui_Approbato
Creator III
Creator III

Try this:

   Date#(   Left(     Filebasename()  , 10 ) , 'YYYY-MM-DD') as Date_Key

 

marcus_sommer
MVP & Luminary
MVP & Luminary

It looked that there is no conversion needed else just removing the non-numeric chars. Therefore try it with:

keepchar(filebasename(), '0123456789')

Beside this I suggest to create a real date, too to be able to cluster them into months/years and so on and to calculate with them - the best for it would be a linking to a master-calendar:

date(date#(keepchar(filebasename(), '0123456789'), 'YYYYMMDD'))

- Marcus

ngreddy1982
Contributor III
Contributor III
Author

All above are working but the output field is still in text for some reason, not converting to numeric format. 

Thank you for your time.

marcus_sommer
MVP & Luminary
MVP & Luminary

Keepchar() is a string-function and therefore is the return a string but usually are only numeric returns interpreted as numbers. Nevertheless you could force a numeric interpretation with something like:

num(num#(keepchar(filebasename(), '0123456789')), '########')

- Marcus

 

View solution in original post

ngreddy1982
Contributor III
Contributor III
Author

Thank you.