10 Replies Latest reply: Dec 14, 2011 7:38 AM by maibangbee RSS

    Master Calendar

      Hi

       

      I have several QVDs with date fields (Year and Month). In other to avoid a loop, I have decided to use a master calendar but its still not working properly (the dates are not joinning) - e.g. when I select 2010, it does not affect the selection, it duplicates some of the data, etc. Could you please help me as its my first time using Master Calendar.

       

      CalendarLinkage:

       

       

      LOAD

        Year_O as [Calendar Year],

        Month_O as [Calendar Month]

      From

      data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

       

       

      CONCATENATE (CalendarLinkage)

      LOAD

        Year_C  as [Calendar Year],

        Month_C as [Calendar Month]

      FROM

      data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);

       

       

       

       

      CONCATENATE (CalendarLinkage)

      LOAD

      Year as [Calendar Year]

      from

       

       

      \\10.193.2.14\qvapps\Live\InlinePurchases_2.xls

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

       

      Thanks in advance

        • Re: Master Calendar

          hi

          apply this:

           

          let vStartDate= num(MakeDate(2010));

          cal1:

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

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

           

           

          cal2:

          load

          *,

          year(Date) as Year,

          Month(Date) as Month,

          Day(Date) as Day,

          //Month(Date) &'-'&Year(Date) as Monthyear,

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

          Resident cal1;

          drop table cal1;

           

          note: instead of 2010 you can replace the year which is your starting year.

          By applying the above script your master calender gets ready now you can associate the dates in the table by aliasing as:

           

          calender linkage:

           

          load *,

          date as Date

          from qvdname;

           

          note: here date is the field which are the dates coming from your data.

           

          you get the table view as:

           

          check PFA

           

           

          hope this makes you clear.

           

           

          regards

          vijit

           

           

           

           

           

           

           

           

           



          • Re: Master Calendar

            As shown in your Doc1 there is no linking between master calender and calender linkage, so do aliasing to associate

            calender linkage table with cal2 as:

             

            calender linkage:

            LOAD

              Year_O as Year,

              Month_O as [Calendar Month]

            From

            data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

             

             

            try this , tell me if the problem persist.

              • Re: Master Calendar

                Hi There

                 

                That seem to work but when I add (Data\SAPMM\Projects\COVP.qvd(Qvd)) - see last statement, I get loop problem "One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

                I have other tables to add with dates... do you know the best way to loose them? The relationship is in the diagram I sent to you earlier.

                 

                Thanks so much for your help

                 

                 

                CalendarLinkage_Sales:

                LOAD

                %InvoiceBillingDocument_Key,

                Year_O as Year,

                Month_O as Month

                From

                data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

                 

                 

                CONCATENATE (CalendarLinkage_Sales)

                LOAD

                %InvoiceBillingDocument_Key,

                Year_C as Year,

                Month_C as Month

                FROM

                data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);

                 

                 

                CONCATENATE (CalendarLinkage_Sales)

                LOAD

                Year as Year

                 

                from


                10.193.2.14\qvapps\Live\InlinePurchases_2.xls

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

                 

                 

                 

                CONCATENATE (CalendarLinkage_Sales)

                LOAD

                ,

                Year_Pur as ,

                Month_Pur as

                From

                Data\SAPMM\Projects\COVP.qvd(Qvd);

              • Re: Master Calendar

                aliasing is the only way to avoid loosely coupled tables, there is a circular reference because of that qlikview create loosely coupled tables.

                 

                check your data modelling and rename the fields which causes circular reference.

                 

                Do one thing attach your original qvw file so that I can check the data modelling.

                • Re: Master Calendar
                  rohit gupta

                  hi try this

                  Calendar: 

                   

                   

                  LET vDateMin = Num(MakeDate(2009,1,1)); 

                  LET vDateMax = Floor(MonthEnd(Today())); 

                  LET vDateToday = Num(Today()); 

                   

                   

                  TempCalendar: 

                  LOAD

                  $(vDateMin) + RowNo() - 1 AS DateNumber, 

                  Date($(vDateMin) + RowNo() - 1) AS TempDate 

                  AUTOGENERATE 1 

                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

                   

                   

                  Calendar: 

                  LOAD

                  Date(TempDate) AS CalendarDate, 

                   

                   

                  // Standard Date Objects

                  Day(TempDate) AS CalendarDayOfMonth, 

                  WeekDay(TempDate) AS CalendarDayName, 

                  Week(TempDate) AS CalendarWeekOfYear, 

                  Month(TempDate) AS CalendarMonthName, 

                  'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 

                  Year(TempDate) AS CalendarYear, 

                  Resident TempCalender Order By TempDate ASC;

                  • Re: Master Calendar

                    After checking your data modelling I think problem is of synthetic table as there are more than one field common in

                    Timesheetorders & forecast .

                     

                    Cost centre

                    Cost rates

                    year

                    key

                     

                     

                     

                    So rename the fields of forecast table something else except cost centre as:

                     

                    forecast:

                    load

                     

                    applymap  ('CostRateMap',[Cost Centre] & [Year],'unknown') & '\' & Year & '\' & [Cost Centre] & '\' & [User_Name] & '\' &  [Task Code] as Key1,

                    applymap  ('CostRateMap',[Cost Centre] & [Year],'unknown') as CostRates1,

                     

                    [Year] as year1

                    from qvdname;

                     

                    And then apply master calender.

                     

                    note:

                    link only one field of master calender with the calender linkage otherwise again synthetic table will form.

                     

                     

                     

                    regards

                    vijit

                      • Re: Master Calendar

                        Thanks for the sync table help.

                         

                        Do i need to include the primary key for each qvd?

                         

                         

                         

                         

                         

                        let vStartDate= num(MakeDate(2010));

                        cal1:

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

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

                         

                         

                        cal2:

                        load

                        *,

                        year(Date) as ,

                        Month(Date) as Calendar Month],

                        Day(Date) as Day,

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

                        Resident cal1;

                        drop table cal1;

                         

                        CalendarLinkage_Sales:

                        LOAD

                        %InvoiceBillingDocument_Key,

                        Year_O as ,

                        Month_O as

                        From

                        data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

                         

                         

                        CONCATENATE (CalendarLinkage_Sales)

                        LOAD

                        %InvoiceBillingDocument_Key,

                        Year_C as ,

                        Month_C as

                        FROM

                        data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);