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

[hic]

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

[prieper]

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

[#4]

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;

[rwunderlich]

Are  Probsummary and Changesummary linked by a common field?

[#6]

ya

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.

[#7]

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

[#8]

[rwunderlich]

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

[#10]

give the name to the mapping table as:

quarter_map:

mapping Load rowno() as Month,

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

AutoGenerate(12);

[#11]

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