26 Replies Latest reply: Jan 20, 2012 3:07 PM by Purna Cheekati RSS

    Master Calender

      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 Kumar

          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
            Tresesco B

            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

              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

                  //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
                  Henric Cronström

                  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

                      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
                          Henric Cronström

                          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

                              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
                                  Henric Cronström

                                  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
                                      Peter Rieper

                                      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,

                                      [code]

                                      LOAD

                                      OPEN_TIME AS Mydate,

                                      'Opening'     AS Criteria,

                                      ....;

                                      CONCATENATE LOAD

                                      ORIG_DATE_ENTERED     AS MyDate,

                                      'Change'     AS Criteria

                                      .....;

                                      [/code]

                                       

                                      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

                              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;