topic Master Calender in QlikView

Master Calender

Wed, 18 Jan 2012 06:11:21 GMT

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. . . .

Master Calender

vijay_iitkgp — Wed, 18 Jan 2012 06:17:55 GMT

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;

Master Calender

tresB — Wed, 18 Jan 2012 06:23:14 GMT

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

Master Calender

vijay_iitkgp — Wed, 18 Jan 2012 06:28:18 GMT

Hey Tresesco,

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

Vijay

Master Calender

Wed, 18 Jan 2012 06:57:34 GMT

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

Master Calender

hic — Wed, 18 Jan 2012 08:55:00 GMT

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

Master Calender

Wed, 18 Jan 2012 09:11:27 GMT

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".

Master Calender

Wed, 18 Jan 2012 09:52:14 GMT

//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?

Master Calender

hic — Wed, 18 Jan 2012 10:02:59 GMT

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

Re: Master Calender

Wed, 18 Jan 2012 12:11:05 GMT

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. . .

Master Calender

hic — Wed, 18 Jan 2012 12:39:28 GMT

You ask "Is there a work around?". The question is still: How should QlikView behave when you click on "Month"? Should QlikView show transactions with this OrderMonth (or Open_Time) or should QlikView show transactions with this DeliveryMonth (or Orig_Date)? Or should QlikView show the union between the two sets?

These are all different behaviours and you must decide which way to go. Once you have decided, you can create a data model that does what you want.

So - yes - there is a work-around.
/HIC

Master Calender

prieper — Wed, 18 Jan 2012 14:51:56 GMT

Dears,

fully agree with Henric.

A mastercalendar makes only sense if referring to (one field in) one table.

As kind of workaround you then should concatenate both tables,

LOAD

OPEN_TIME AS Mydate,

'Opening' AS Criteria,

....;

CONCATENATE LOAD

ORIG_DATE_ENTERED AS MyDate,

'Change' AS Criteria

.....;

The mastercalendar should then refer to "MyDate". Each selection there should then deliver some plausible results, further selections might be done by "Criteria".

HTH

Peter

Master Calender

Wed, 18 Jan 2012 17:57:37 GMT

sorry i have done a mistake replace cal2 table code with the below code:

cal2:

load

year(Date) as Year,

Month(Date) as Month,

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

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;

Master Calender

rwunderlich — Wed, 18 Jan 2012 19:34:48 GMT

Are Probsummary and Changesummary linked by a common field?

Master Calender

Thu, 19 Jan 2012 04:58:22 GMT

Probsummary has

date(Open_time) as Date

Changesummary has

Orig_Date as Date

and

mastercalender has

Date

so Probsummary and Changesummary both are linked to same field "Date" in mastercalender.

mastercalender contains all the dates which are present in both the tables i.e Probsummary and Changesummary .

hope this makes you clear.

Re: Master Calender

Thu, 19 Jan 2012 05:02:32 GMT

No Rob Wunderlich, there is no relation.

Henric Cronström, Thanks. . .

The behaviour should be something like this, If there is a selection of a Month (from master calender) then the selection should reflect across Probsummary and changesummary (Independently). . . When you say Union for that there has to be a relation b/w Probsummary and Changesummary which is not present.

vijit jindal, I am getting the below error now: -

Field not found - <Month>

Cal2:

load*,

year(Date) as Year,

Month(Date) as Month,

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

Day(Date) as Day,

WeekDay(TempDate) as WeekDay,

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

Resident Cal1

The exact Master code is : -

let vStartDate= num(MakeDate(2011)); // 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*,

year(Date) as Year,

Month(Date) as Month,

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

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

Resident Probsummary;

Changesummary:

load *,

date(ORIG_DATE_ENTERED) as Date // to create linking

Resident Changesummary;

P.S: Thank you all for your continuous help. . .

Regards,

Sridhar

Master Calender

Thu, 19 Jan 2012 05:14:45 GMT

Re: Master Calender

rwunderlich — Thu, 19 Jan 2012 05:28:49 GMT

I recommend you try Qlikview Components (http://qlikviewcomponents.org). The entire script to generate your calendar would be:

$(Include=..\qvc_runtime\qvc.qvs);

CALL Qvc.CalendarFromField('OPEN_TIME');

CALL Qvc.CalendarFromField('ORIG_DATE_ENTERED');

-Rob

http://qlikviewcomponents.org

Master Calender

Thu, 19 Jan 2012 05:47:00 GMT

give the name to the mapping table as:

quarter_map:

mapping Load rowno() as Month,

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

AutoGenerate(12);

Master Calender

Thu, 19 Jan 2012 08:56:09 GMT

I still have this error. . .

I had named the mappping table quarter_map even after that i got the below error . . .

Field not found - <Month>

cal2:

load *,

year(Date) as Year,

Month(Date) as Month,

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

Day(Date) as Day,

WeekDay(TempDate) as WeekDay,

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

Resident cal1