4 Replies Latest reply: Mar 18, 2016 11:34 AM by Manisha Dutta RSS

    Master Calendar Problem

    Manisha Dutta

      Hi, i am new to Qlikview. I have created a master calendar but year column which contains 2 years data (2011 & 2012)  is not working properly. It's providing same result for both years on selecting a particular year.

      VisitedSites:

      LOAD Agency,

           Platform,

           Url,

           [Date Sampled],

         date(floor(timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Date],

      time(frac (timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Time],

      [Likes/Followers/Visits/Downloads]    

          

      FROM

      [C:\Users\at\Desktop\For Qlik\NYC_Social_Media_Usage.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)where Platform <> 'TOTAL' ;

       

      Temp:

      Load

                     min([Record_Date]) as minDate,

                     max([Record_Date]) as maxDate

      Resident VisitedSites;

       

      Let varMinDate = '8/1/2011';

      Let varMaxDate ='12/31/2012';

      DROP Table Temp;

      Datefield:

      LOAD

         date(date#('$(varMinDate)')-1 + recno())  as Datefield

      AUTOGENERATE (date#('$(varMaxDate)')-date#('$(varMinDate)'))+1;

       

      Calendar:

      LOAD

          Datefield as %Date,

          date(Datefield,'YYYY-MMM-DD') as Date,

          year(Datefield) as Year,

          month(Datefield) as Month,

          day(Datefield) as Day,

          week(Datefield) as Week,

          weekday(Datefield) as Weekday,

          'Q' & ceil(month(Datefield) / 3) AS Quarter,

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

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

          

                     ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

           yearname (Datefield, 0, 10 ) as FiscalYear

       

       

      RESIDENT Datefield;

       

      drop table Datefield;

        • Re: Master Calendar Problem
          Stefan Wühl

          What is the key field between your calendar and VisitedSites table?

          If there is no link, selections in calendar will have no effect on your other tables at all.

          • Re: Master Calendar Problem
            Sunny Talwar

            Your date field from calendar isn't linked to the date field in your VisitedSites table. Try this:

             

            Calendar:

            LOAD

                Datefield as [Record_Date],

                date(Datefield,'YYYY-MMM-DD') as Date,

                year(Datefield) as Year,

                month(Datefield) as Month,

                day(Datefield) as Day,

                week(Datefield) as Week,

                weekday(Datefield) as Weekday,

                'Q' & ceil(month(Datefield) / 3) AS Quarter,

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

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

               

                           ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

                 yearname (Datefield, 0, 10 ) as FiscalYear

            RESIDENT Datefield;

             

            drop table Datefield;

            • Re: Master Calendar Problem
              Ruben Marin

              Hi Manisha, you need to link your calendar to the VisitedSites table.

               

              Tables are linked when they have fields with the same name,to make it simpler and don't change anything you already loaded, you can create another field in your calendar to link the tables:

              Calendar:

              LOAD

                  Datefield as %Date,

                  date(Datefield,'YYYY-MMM-DD') as Date,

                  date(floor(timestamp#(Datefield,'MM/DD/YYYY hh:mm'))) as [Record_Date],

                 date(Datefield) as [Record_Date]

              ...

               

              Edit: Trying to keep it simple I made it wrong, Sunny is right, the timestamp# will make my script to fail