Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
How about trying makedate() function.
Try this:
Date#( Left( Filebasename() , 10 ) , 'YYYY-MM-DD') as Date_Key
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
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.
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
Thank you.