Master Calender

[#1]

Hi All,

I have an issue with master calender.

I need to develop one master calender.

I have two major tables, Probsummary and Changesummary.

Both the tables have two DIFFERENT date columns, Probsummary:- Open_time and ChangeSummary: Orig_Date

Now I need a central calendar from where I should be able to extract Year,Quarter, Month, Weekofday, Week etc. . . .

And i need the selections made on these calendar fields to reflect across changesummary and probsummary.

I'm fairly new to ETL side of Qlikview therefore i'm in need of guidance regarding this.

I would want to know the best way to approach this. . .

Few standard scripts would also help. . . .

[vijay_iitkgp]

Hi,

You can first define the startdate and enddate from your existing date field and then use forllowing script to create master calendar.After that link both Open_time and orig date to this calendar.Hope this will help.

LET varMinDate = StartDate; eg:'6/15/2001'

LET varMaxDate = EndDate; eg: Today();

//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************Master Calendar************
MasterCalendar:
LOAD
TempDate AS Date,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear

Resident TempCalendar
Order by TempDate ASC;

Drop Table TempCalendar;

[tresesco]

Hi,

You can have a look at http://community.qlik.com/message/114947#114947

specially at the last post of the link.

Hope this would help you.

Regards,  tresesco

[vijay_iitkgp]

Hey Tresesco,

Good to c you here. Where r u these days.

Vijay

[#5]

you can create master calender as:

let vStartDate= num(MakeDate(2010));        // start year of your calender

mapping Load rowno() as Month,

'Q' & ceil(rowno()/3) as Quarter

AutoGenerate(12);

cal1:

load Date($(vStartDate)+RowNo()-1) as Date

AutoGenerate(num(today())- $(vStartDate)+1);

cal2:

load

*,

applymap('quarter_map',num(Month(TempDate)),null()) as Quarter,

year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day,

WeekDay(TempDate) as WeekDay,

dual((Month(Date) &'-'&Year(Date)),num(MakeDate(year(Date),month(Date)))) as Monthyear

Resident cal1;

drop table cal1;

Probsummary:

load *,

date(Open_time) as Date        // to create linking with master calender dates

from ............;

ChangeSummary:

load *,

date(Orig_Date) as Date        // to create linking

from .............;

regards

vijit

[hic]

I would create two Master Calendar tables. One for "Open_time" and one for "Orig_Date".

If these dates really are two different dates then it is clearer for the end-user if you present them in different list boxes. E.g. "Month_for_Open_Time" may well be 'February' also when you have selected 'January' as "Month_for_Orig_Date".

/HIC

[#7]

master calender must be only one and you can associate as much table to it as you want through a common field Date.....when you click on dates of "open_time" it picks only those dates from the master calender which is in "open_time".

[#8]

//This is my first table:-

Probsummary:

LOAD NUMBER as P_Number,

     OPEN_TIME, //This is date which I have to use for this table

     OPENED_BY,

     PRIORITY_CODE as P_Priority,

     ASSIGNMENT,

     LOCATION as P_Location,

     ALERT1,

     IF(ALERT1='1','Response Breached','Within Response') as [Resonse SLA],

     ASSIGNEE_NAME as Technician,

     CONTACT_NAME,

     INCIDENT_ID,

     PROBLEM_STATUS,

     SUBCATEGORY as P_SubCategory,

     PRODUCT_TYPE as P_ProductType,

     PROBLEM_TYPE as P_ProblemType,

     COUNTRY as P_Country

FROM

[HPSM_Data 10th Jan.xls]

(biff, embedded labels, table is ProbsummaryM1$);

Join

LOAD NUMBER as P_Number,

     SLA_BREACH,

     AFFECTED_SERVICES as P_AffectedServices,

     AFFECTED_ITEM as P_AffectedItem,

     ZMANNED as P_ZMANNED,

     IF(ZMANNED='t','Yes','No') as [Manned Site?],

     ZITC as P_ZITC,

     IF(ZITC='t','INFRA','APPLICATION') as Select

FROM

[HPSM_Data 10th Jan.xls]

(biff, embedded labels, table is ProbsummaryM2$);

STORE Probsummary into Probsummary.qvd;

Second table:-

Changesummary:

LOAD NUMBER as C_Number,

     CATEGORY,

     STATUS,

     REQUESTED_BY,

     ASSIGNED_TO,

     ASSIGN_DEPT,

     COORDINATOR,

     CURRENT_PHASE,

     PRIORITY_CODE as C_Priority,

     OPERATOR ,

     ORIG_DATE_ENTERED, //This is date which I have to use for this table

     ORIG_OPERATOR,

     SUBCATEGORY,

     LOCATION as C_Location,

     AFFECTED_SERVICES as C_AffectedServices

FROM

[HPSM_Data 10th Jan.xls]

(biff, embedded labels, table is CM3RM1$);

join

LOAD NUMBER as C_Number,

     SEVERITY as C_Severity,

     AFFECTED_ITEM as C_AffectedItem,

     REQUESTEDDATE,

     LOCATION_FULL_NAME as C_LocationFullName,

     ZINCIDENT_ID,

     ZITC as C_ZITC,

     IF(ZITC='t','INFRA','APPLICATION') as ChngSelect

FROM

[HPSM_Data 10th Jan.xls]

(biff, embedded labels, table is CM3RM2$);

store Changesummary into Changesummary.qvd;

Now Should I load the Mastercalender and then link my loaded tables?

1.  Because I have already loaded the tables so for the sake of linking them to calender dates should i use a resident Load?

2 When I try an aggregation, Count(NUMBER) {I do group by of all the columns when i do count} at script level . . I'm getting the wrong count. . But if i try at the document level it is correct . . . any specific reason?

[hic]

I agree that you should always try to use only one Calendar table - if possible.

However, if your data model contains two dates, e.g. OrderDate and DeliveryDate, then you face the question how a selection in the Calendar table, e.g. in the field "Month" should be interpreted: Should QlikView's logical inference pick out transactions with OrderDate in this month, or should it pick out transactions with DeliveryDate in this month? The two cases correspond to two different data models and QlikView cannot know what the end user means.

All I am saying is that it is possible to include several Calendar tables in the same QlikView app, and I think that sometimes this is the best solution.
/HIC

[#10]

Thank you @Henric Cronström

But my requirement says there has to be only one selection for month/year/week etc. . . They should reflect for both. . . Is there a work around?

Hi vijit jindal

I tried you solution I got the following error:-

Field not found - <TempDate>

Cal2:

load*,

applymap('quarter_map',num(Month(TempDate)),null()) as Quarter,

year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day,

WeekDay(TempDate) as WeekDay,

dual((Month(Date) &'-'&Year(Date)),num(MakeDate(year(Date),month(Date)))) as Monthyear

Resident Cal1

Rest everything got loaded properly. .

I ll attach a screenshot also . . . to show my data model. . .