Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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?
Hi Yesmine,
In the script use
Let us say this Date field is in your table Name as 'Table'
Table:
load other fields,
Date(floor(num(DATDOM)),'DD/MM/YYYY') as DATDOM
from your tableName
For creating master calendar use:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DATDOM) as minDate,
max(DATDOM) as maxDate
Resident Table
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
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
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Regards
Kamal
Load like this in the script....
Date(DATDOM, 'DD/MM/YYYY') as DATDOM
Hi Yesmine,
In the script use
Let us say this Date field is in your table Name as 'Table'
Table:
load other fields,
Date(floor(num(DATDOM)),'DD/MM/YYYY') as DATDOM
from your tableName
For creating master calendar use:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DATDOM) as minDate,
max(DATDOM) as maxDate
Resident Table
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
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
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Regards
Kamal
Hi Kamal,
That was very helpful. I got exactly what I wanted.
Thank you
Thanks Yasmine.
Regards
Kamal