Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How to create Master Calander for below data.
Hi Abc a (!!!!)
Here is the simple method.
Hope this helps.
Hi,
If you want to create the Master calendar with all dates in the years. then you can use the below script,
Transactions:
LOAD * INLINE [
%TransDate, TransType, TransValue
2012-01-12, Sale, 100
2012-02-01, Sale, 100
2012-03-05, Sale, 100
2012-03-31, Payment, 200
2012-04-29, Sale, 150
2012-05-19, Sale, 175
2012-06-02, Sale, 200
2012-06-30, Payment, 400
2012-07-03, Sale, 200
2012-08-13, Sale, 100
2012-09-22, Sale, 200
2012-09-30, Payment, 400
2012-10-10, Sale, 175
2012-11-14, Sale, 225
2012-12-02, Sale, 325
2012-12-31, Payment, 400
];
Max_Min_Dates:
Load
Max(Date#(%TransDate,'YYYY-MM-DD')) AS MaxDate,
Min(Date#(%TransDate,'YYYY-MM-DD')) AS MinDate
Resident Transactions;
Let VarMinDate =Num(YearStart(Peek('MinDate',0,'Max_Min_Dates')));
Let VarMaxDate =Num(YearEnd(Peek('MaxDate',0,'Max_Min_Dates')));
Drop Table Max_Min_Dates;
Temp_Calendar:
Load
Date($(VarMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(VarMinDate) + IterNo() -1 <= $(VarMaxDate);
Master_Calendar:
Load
TempDate AS [%TransDate],
Month(TempDate) AS [CalendarMonth],
Year(TempDate) AS [CalendarYear],
MonthName(TempDate) AS [CalendarYearMonth],
Week(TempDate) AS [WeekNo]
Resident Temp_Calendar
Order by TempDate;
Drop Table Temp_Calendar;
Hi,
Please Check the attachment .I hope this helps you.
Thanks,
Bunny
Please check the attached file with solution:
What I've done was based on the following qlikview posts on the community: