Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi friends...
i am new in qlikview. please help me for building calender as i have three tables and all three tables having date columns with different names so how to build a calender as i have to consider all three date consider...
Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.
tempMaxDate:
Load
Max(Date1) As MaxDate,
Min(Date1) As MinDate
Resident A;
Concatenate
Load
Max(Date2) As MaxDate,
Min(Date2) As MinDate
Resident B;
Concatenate
Load
Max(Date3) As MaxDate,
Min(Date3) As MinDate
Resident C;
MaxDate:
Load
Max(MaxDate) As MaxDate,
Min(MinDate) As MinDate
Resident tempMaxDate;
Drop Table tempMaxDate;
Let vMaxDate = Peek('MaxDate', 0, 'MaxDate');
Let vMinDate = Peek('MinDate', 0, 'MaxDate');
Cal:
LOAD
Year(Date($(vMinDate) + RecNo() - 1)) As Year,
Month(Date($(vMinDate) + RecNo() - 1)) AS Month,
'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,
Day($(vMinDate) + RecNo() - 1) AS Day,
Date($(vMinDate) + RecNo() - 1) AS CalendarDate
AutoGenerate vMaxDate - vMinDate +1 ;
Drop Table MaxDate;
Hi Ashish
you can use below code for the Calendar also you can go threw with the blog on Qlickview. as per your requirement you can create calendar control.
see the below generic code for your review. that i was using in my application. let me know if it work for you ore else i can create as per your requirement.
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Num(MakeDate(2001,12,31));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS [Calendar Date],
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS Month,
num(Month(TempDate)) AS MonthNum,
Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate))) as CalendarYYYYMM,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS Year,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
Date(MonthStart(TempDate)) as CalendarMonthStart,
Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Hi,
use below code
Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
WeekYear(TempDate) as TestWeek,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
year(TempDate)&'-'&Month(TempDate) as Year_Month,
MonthName(TempDate) as YearMonth,
Day(TempDate) AS Day,
'Q'&ceil(month(TempDate)/3) AS Quarter
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);
Regards
ASHFAQ
thanx Pradip , i have tried this one but its showing error message as-
Script line error:
cal:
LOAD
Year(Date( + RecNo() - 1)) As Year,
Month(Date( + RecNo() - 1)) AS Month,
'Q' & Ceil(Month(Date( + RecNo() - 1))/3) As Quarter,
Day( + RecNo() - 1) AS Day,
Date( + RecNo() - 1) AS CalendarDate
AutoGenerate vMaxDate - vMinDate +1
Hi,
did you try my code.
Regards
ASHFAQ
ya, i have tried this one also but its showing wrong output...
Thanx but, its showing wrong output...
Try with this:
Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.
tempMaxDate:
Load
Max(Date1) As MaxDate,
Min(Date1) As MinDate
Resident A;
Concatenate
Load
Max(Date2) As MaxDate,
Min(Date2) As MinDate
Resident B;
Concatenate
Load
Max(Date3) As MaxDate,
Min(Date3) As MinDate
Resident C;
MaxDate:
Load
Max(MaxDate) As MaxDate,
Min(MinDate) As MinDate
Resident tempMaxDate;
Drop Table tempMaxDate;
Let vMaxDate = Peek('MaxDate', -1, 'MaxDate');
Let vMinDate = Peek('MinDate', -1, 'MaxDate');
Cal:
LOAD
Year(Date($(vMinDate) + RecNo() - 1)) As Year,
Month(Date($(vMinDate) + RecNo() - 1)) AS Month,
'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,
Day($(vMinDate) + RecNo() - 1) AS Day,
Date($(vMinDate) + RecNo() - 1) AS CalendarDate
AutoGenerate vMaxDate - vMinDate +1 ;
Drop Table MaxDate;