FileBaseName to _DateKey

[ngreddy1982]

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. 

[stevejoyce]

How about trying makedate() function.

[Gui_Approbato]

Try this:

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

[marcus_sommer]

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]

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 · Accepted solution]

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

[ngreddy1982]

Thank you.