7 Replies Latest reply: Feb 28, 2016 7:39 PM by jagan mohan rao appala RSS

    Master calendar - field not found

    Dawid Nawrot

      Hi,

       

      I have had a look at the Master Calendar video as well as I read the previous discussion but still can't solve my problem.

       

      I have a simple Direct query that looks like following:

       

      Aggregation:

      LIB CONNECT TO 'REPORTING';

      DIRECT QUERY

      DIMENSION

        NATIVE('CONVERT(DATE, ActivityDate)') AS ActivityDay

      MEASURE

        NumberOfSales

      FROM <table>

       

      And then I copied the master calendar script replacing this:

       

       

      Temp: 

      Load 

      min(ActivityDay) as minDate, 

      max(ActivityDay) as maxDate 

      Resident Aggregation;

       

      But I'm still getting fiel not found error.. any idea why?

       

      (Field not found - <<=>: TempCalendar: LOAD + Iterno()-1 As Num, Date( + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=)

        • Re: Master calendar - field not found
          jagan mohan rao appala

          Hi,

           

          Can you paste the whole master calendar generation script? 

           

          Regards,

          Jagan.

          • Re: Master calendar - field not found
            balraj ahlawat

            This script looks okay,  error might be in TempCalender table as highlighted by Qlik Engine

             

            (Field not found - <<=>: TempCalendar: LOAD + Iterno()-1 As Num, Date(DateField is Missing here + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=)


            Share the script of TempCalender Table

              • Re: Master calendar - field not found
                Dawid Nawrot

                There it is:

                 

                QuartersMap: 

                MAPPING LOAD  

                rowno() as Month, 

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

                AUTOGENERATE (12); 

                     

                Temp: 

                Load 

                min(ActivityDay) as minDate, 

                max(ActivityDay) as maxDate 

                Resident Aggregation; 

                     

                Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                DROP Table Temp; 

                     

                TempCalendar: 

                LOAD 

                $(varMinDate) + Iterno()-1 As Num, 

                Date($(varMinDate) + IterNo() - 1) as TempDate 

                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                     

                MasterCalendar: 

                Load 

                  TempDate AS ActivityDay, 

                  week(TempDate) As Week, 

                  Year(TempDate) As Year, 

                  Month(TempDate) As Month, 

                  Day(TempDate) As Day, 

                  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                  Week(weekstart(TempDate)) & '-' & (TempDate) as WeekYear, 

                  WeekDay(TempDate) as WeekDay 

                Resident TempCalendar 

                Order By ActivityDay ASC; 

                Drop Table TempCalendar; 

                  • Re: Master calendar - field not found
                    jagan mohan rao appala

                    Hi,

                     

                    Check this link

                     

                    Master Calendar Generation Script

                     

                    Regards,

                    jagan.

                      • Re: Master calendar - field not found
                        Dawid Nawrot

                        The data load doesn't fail now, but how do I join this to my Aggregation table?

                         

                        If I rename the "date" field to match my aggregation I guess the link is created automatically but if I place Week or Month on the visualisation, it's empty?

                         

                        During data load it says CalendarMaster << Aggregation

                        Lines fetched: 0

                         

                        even though the aggregation data loaded correctly

                          • Re: Master calendar - field not found
                            Sangram Reddy

                            Hi Dawid,

                             

                            Try this script:

                            1. QuartersMap: 
                            2. MAPPING LOAD  
                            3. rowno() as Month, 
                            4. 'Q' & Ceil (rowno()/3) as Quarter 
                            5. AUTOGENERATE (12); 
                            6.  
                            7. Temp: 
                            8. Load 
                            9.                min(OrderDate) as minDate, 
                            10.                max(OrderDate) as maxDate 
                            11. Resident Orders; 
                            12.  
                            13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
                            14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
                            15. DROP Table Temp; 
                            16.  
                            17. TempCalendar: 
                            18. LOAD 
                            19.                $(varMinDate) + Iterno()-1 As Num, 
                            20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
                            21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
                            22.  
                            23. MasterCalendar: 
                            24. Load 
                            25.                TempDate AS OrderDate, 
                            26.                week(TempDate) As Week, 
                            27.                Year(TempDate) As Year, 
                            28.                Month(TempDate) As Month, 
                            29.                Day(TempDate) As Day, 
                            30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
                            31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
                            32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
                            33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
                            34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
                            35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
                            36.                WeekDay(TempDate) as WeekDay 
                            37. Resident TempCalendar 
                            38. Order By TempDate ASC; 
                            39. Drop Table TempCalendar; 


                                   Thanks,

                                    Sangram.

                            • Re: Master calendar - field not found
                              jagan mohan rao appala

                              Hi,

                               

                              I think the date format is not the same in both the tables, check it and adjust accordingly.

                               

                              Regards,

                              jagan.