Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a basic problem, I would like to create a Master Calendar. But I have problems in how to convert my "Date" in a adequate format.
I have a timestamp as Date in the format 'YYYY-MM-DD hh:mm:ss' and I would use this field to make my Master Calendar, for that I think I must convert it in a new field with the format 'DD.MM.YYYY' ==> I don`t now how to convert a timestamp in the dateformat which I mentioned above. For Example my basic timestamp of an excel file is ==> 2008-09-22 10:24:54 ==> must convert it in the format ==> 22.09.2008
And with this field with the right format than I can make a calendar which is described for example in the QlikView Cookbook.
Basic Setup of the Application
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
I hope somebody could help me? Thanks
Herbert,
Dates are numeric in Excel, so the next sholud be working, given you set up your date format already:
date(floor(Date)) as Date
Regards,
Michael
Hi. Herbert.
it's easy. 🙂
ex:
Transactions:
LOAD
field1,
field2,
date(fieldDate) as myDate
FROM ....
then make a calendar table:
MasterCalendar:
LOAD myDate,
year(myDate) as Year,
month(myDate) as Month
RESIDENT Transactions;
best regards, sparur.
Herbert,
Dates are numeric in Excel, so the next sholud be working, given you set up your date format already:
date(floor(Date)) as Date
Regards,
Michael
Hi
use Date(fieldname,'DD/MM/YYYY') AS Date
I think it ll help u..
in this case does not necessarily indicate the format.
because the desired format specified in the basic constants of QV Document(SET DateFormat='DD.MM.YYYY';)
Thanks for the answers, I have tried several types of date formatting before, but the date function in combination with the floor function brings me the right result, thanks to Solomovich and all the others for the help.
The problem with the other date functions was, that after generating a MasterCalendar for every Day also without Data ==> with the autogenerate function ==> there were no connection between the autogenerated data and my basic Timestamp Data, now it works fine.
Thanks to all
Hi Sparur,
That 1 is qlikview default. Based on our data we need to change the formart. as per ur data u need to use date(fieldname(),'DD/MM.YYYY')
hi, Sathish.
not quite understand what you mean? I understand the function date () just takes the default value of the format from constant document: SET DateFormat...
I am wrong?
I am very new to qlikview. I think with the date () function only we get not rid of the hh:mm:ss but the floor() function do this and the date() function than takes the default setting like DD.MM.YYYY, but I can be wrong. But with only the date function there was also the hh:mm:ss and thats a big problem when I want to make a calendar.
Thanks for all your help
That's right, because floor() rounding of date, so we get only date(without time)