Can someone please help me with script to create a master calendar from the dimension order date from orders table?

[#1]

vinay_bangari‌

[Anonymous]

can you please post sample app.

[#3]

vinay_bangari‌

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);

[settu_periasamy]

Hi,

check this link..

How to create a Calendar

[qlikviewwizard]

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);

[qlikviewwizard]

Hi,

Please check Rob's Calendar application. Hope this also will be useful.

Search Recipes | Qlikview Cookbook

[engishfaque]

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

[Anonymous · Accepted solution]

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;