Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. . . .
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
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
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;
Are Probsummary and Changesummary linked by a common field?
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.
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
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
give the name to the mapping table as:
quarter_map:
mapping Load rowno() as Month,
'Q' & ceil(rowno()/3) as Quarter
AutoGenerate(12);
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