5 Replies Latest reply: Feb 11, 2016 12:46 PM by Mitali Ajgaonkar RSS

    storing in mastertable

    Mitali Ajgaonkar

      p1.png

      I have a column newdate. I want to create a mastercalendar and store the frequency of dates (if a date occurs 3 times in new date column its frequency should be 3, if there is no entry for a particular day its frequency should be zero). The purpose of doing this is i want to make bar chart and line chart of number of tweets per day, per month etc. So i want to store data in mastercalendar. The bar should show zero for a particular day if there was no date entry in new date column on that day and 3 if the date is three times in new date column.

        • Re: storing in mastertable
          Marco Wedel

          please post sample data and exprected result. Best as a QlikView application.

           

          thanks

           

          regards

           

          Marco

          • Re: storing in mastertable
            Mitali Ajgaonkar

            QuartersMap: 

            MAPPING LOAD  

            rowno() as Month, 

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

            AUTOGENERATE (12); 

             

            Temp: 

            Load 

                           min(onlydate) as minDate, 

                           max(onlydate) as maxDate

            Resident newtab; 

             

            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 OrderDate, 

                           week(TempDate) As Week, 

                           Year(TempDate) As Year, 

                           Month(TempDate) As Month, 

                           Day(TempDate) As Day, 

                           YeartoDate(TempDate)*-1 as CurYTDFlag, 

                           YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                           date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

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

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

                           WeekDay(TempDate) as WeekDay 

            Resident TempCalendar 

            Order By TempDate ASC; 

            Drop Table TempCalendar; 

             

             

            is this master calendar right? i have dates from 7/10/2015 till 2/8/2016  in my database but it creates master calendar only from 12/29/2014 till 2/26/2015