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

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

Mon, 31 Aug 2015 20:23:12 GMT

vinay_bangari‌

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

Anonymous — Mon, 31 Aug 2015 20:27:33 GMT

can you please post sample app.

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

Mon, 31 Aug 2015 21:15:59 GMT

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

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

settu_periasamy — Tue, 01 Sep 2015 02:21:09 GMT

Hi,

check this link..

How to create a Calendar

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

qlikviewwizard — Tue, 01 Sep 2015 02:38:31 GMT

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

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

qlikviewwizard — Tue, 01 Sep 2015 02:59:06 GMT

Hi,

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

Search Recipes | Qlikview Cookbook

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

engishfaque — Tue, 01 Sep 2015 04:17:44 GMT

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

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

Anonymous — Tue, 01 Sep 2015 12:29:50 GMT

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;