Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
tmp_MaxDate:
Load min(OrderDate) as mindate,
max(OrderDate) as maxdate
Resident Orders ;
LET vMinDate = Peek('mindate',0,'tmp_MaxDate');
LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');
LET vToday = $(vMaxDate);
LET vAsOfDate = num(Today())-1;
drop table tmp_MaxDate;
drop Table Orders;
//********************* Temporary Calendar ***********************//
tmp_CALENDAR:
LOAD
Date($(vMinDate) + RowNo() - 1) AS tmp_Date
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
//mapping tables==============
MapDOW_master:
Mapping LOAD * INLINE [
Day, Day of Week
1, 1
2, 2
3, 3
4, 4
5, 5
6, 5
7, 5
];
//********************* Master Calendar ***********************//
Calendar:
LOAD
tmp_Date AS OrderDate,
Week(tmp_Date) AS Week, //a week is Monday through Sunday
Year(tmp_Date) AS Year,
Month(tmp_Date) AS Month,
'Q' & ceil(Month(tmp_Date)/3) AS Quarter,
Day(tmp_Date) AS Day,
text(WeekDay(tmp_Date)) AS WeekDay,
Month(tmp_Date)*100+Day(tmp_Date) as DayOfYear,
//ApplyMap('MapWeekDayNo',WeekDay(tmp_Date)) AS WeekdayNo,
Date(monthstart(tmp_Date), 'MMM-YYYY') AS MonthYear,
Year(tmp_Date) & num(Month(tmp_Date), '00') AS YearMonth,
WeekYear(tmp_Date) & num(Week(tmp_Date), '00') AS YearWeek,
if(Year(tmp_Date)=WeekYear(tmp_Date), Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1, Week(tmp_Date) + 52 - Week(MonthStart(tmp_Date)) + 1) AS [Week in Month], //week number within a month, 1, 2, 3, 4, 5, 6
InYearToDate(tmp_Date, $(vToday), 0) * -1 AS CurYTDFlag,
InYearToDate(tmp_Date, $(vToday), -1) * -1 AS LastYTDFlag,
InMonthToDate(tmp_Date, $(vToday), 0) * -1 as CurMTD_flag,
InMonthToDate(tmp_Date, $(vToday), -1) * -1 as LastMTD_flag
RESIDENT tmp_CALENDAR
ORDER BY tmp_Date ASC;
DROP TABLE tmp_CALENDAR;
left join (Calendar)
LOAD WeekDay,
WeekDayNo INLINE [
WeekDay, WeekDayNo
Mon, 1
Tue, 2
Wed, 3
Thu, 4
Fri, 5
Sat, 6
Sun, 7
];
store Calendar into [$(vQVDpath)Calendar.qvd] (qvd);
drop Table Calendar;
can you please post sample app.
I have my script this way and I have to create a calendar using the order date from the script.
Orders:
LOAD OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
ShipperID
FROM
[$(vQVDPath)Orders.qvd]
(qvd)
Where Year(OrderDate)>= ($(vMaxYear)-2);
Hi Nithin,
Create new table Calendar as shown below.Some of the dates might be missed in OrderDate field.
Hope this will help this script. Thank you.
LET vDateMin=num(makedate(2013,01,01));
LET vDateMax=floor(monthend(today()));
LET vDateToday = num(today());
Calender:
LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS BillingDate,
month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarMonth,
year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarYear,
monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarMonthname
AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);
Hi,
Please check Rob's Calendar application. Hope this also will be useful.
Dear Nithin,
Try this one,
Calender:
LOAD Date(TempDate) as Date,
Month(TempDate) as Month,
Year(TempDate) as Year
Where Year(TempDate) > 2012; //write your variable name instead 2012;
LOAD RecNo() - 1 + Floor(MakeDate(2012)) as TempDate
AutoGenerate(730);
Kind regards,
Ishfaque Ahmed
tmp_MaxDate:
Load min(OrderDate) as mindate,
max(OrderDate) as maxdate
Resident Orders ;
LET vMinDate = Peek('mindate',0,'tmp_MaxDate');
LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');
LET vToday = $(vMaxDate);
LET vAsOfDate = num(Today())-1;
drop table tmp_MaxDate;
drop Table Orders;
//********************* Temporary Calendar ***********************//
tmp_CALENDAR:
LOAD
Date($(vMinDate) + RowNo() - 1) AS tmp_Date
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
//mapping tables==============
MapDOW_master:
Mapping LOAD * INLINE [
Day, Day of Week
1, 1
2, 2
3, 3
4, 4
5, 5
6, 5
7, 5
];
//********************* Master Calendar ***********************//
Calendar:
LOAD
tmp_Date AS OrderDate,
Week(tmp_Date) AS Week, //a week is Monday through Sunday
Year(tmp_Date) AS Year,
Month(tmp_Date) AS Month,
'Q' & ceil(Month(tmp_Date)/3) AS Quarter,
Day(tmp_Date) AS Day,
text(WeekDay(tmp_Date)) AS WeekDay,
Month(tmp_Date)*100+Day(tmp_Date) as DayOfYear,
//ApplyMap('MapWeekDayNo',WeekDay(tmp_Date)) AS WeekdayNo,
Date(monthstart(tmp_Date), 'MMM-YYYY') AS MonthYear,
Year(tmp_Date) & num(Month(tmp_Date), '00') AS YearMonth,
WeekYear(tmp_Date) & num(Week(tmp_Date), '00') AS YearWeek,
if(Year(tmp_Date)=WeekYear(tmp_Date), Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1, Week(tmp_Date) + 52 - Week(MonthStart(tmp_Date)) + 1) AS [Week in Month], //week number within a month, 1, 2, 3, 4, 5, 6
InYearToDate(tmp_Date, $(vToday), 0) * -1 AS CurYTDFlag,
InYearToDate(tmp_Date, $(vToday), -1) * -1 AS LastYTDFlag,
InMonthToDate(tmp_Date, $(vToday), 0) * -1 as CurMTD_flag,
InMonthToDate(tmp_Date, $(vToday), -1) * -1 as LastMTD_flag
RESIDENT tmp_CALENDAR
ORDER BY tmp_Date ASC;
DROP TABLE tmp_CALENDAR;
left join (Calendar)
LOAD WeekDay,
WeekDayNo INLINE [
WeekDay, WeekDayNo
Mon, 1
Tue, 2
Wed, 3
Thu, 4
Fri, 5
Sat, 6
Sun, 7
];
store Calendar into [$(vQVDpath)Calendar.qvd] (qvd);
drop Table Calendar;