Discussion board where members can get started with QlikView.
I am new with QlikView, I need some help to create my table Calendar.
The current date format is: dd/mm/yyyy 00:00:00
I want to delete the time and only keep the date with the format: DD/MM/YYYY
Then I want to create the table Calendar with the different date representations: Year, Month, MonthYear, Quarter...
Any suggestion please?
Go to Solution.
In the script use
Let us say this Date field is in your table Name as 'Table'
load other fields,
Date(floor(num(DATDOM)),'DD/MM/YYYY') as DATDOM
from your tableName
For creating master calendar use:
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
min(DATDOM) as minDate,
max(DATDOM) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Order By TempDate ASC;
Drop Table TempCalendar;
Load like this in the script....
Date(DATDOM, 'DD/MM/YYYY') as DATDOM
That was very helpful. I got exactly what I wanted.