6 Replies Latest reply: Jun 4, 2013 8:36 AM by Rodrigo Giner RSS

    ApplyMap on Master Calendar is not working

    Rodrigo Giner

      I have a Mapping Table with the amount of workhours per Year and Month Period, and I wanna asociate it with the MasterCalendar, making a field in the master calendar with the amount of hours per month.

       

      WorkHours:

      Mapping LOAD * INLINE [

          Month, Hours

          201301, 186

          201302, 152

          201303, 160

          201304, 160

          201305, 176

          201306, 144

          201307, 176

          201308, 168

          201309, 168

          201310, 176

          201311, 168

          201312, 168

      ];

       

       

      Calendar:

      LOAD

          Date(TempDate) AS CalendarDate,

          Date(TempDate,'YYYYMM') AS Period,

          ApplyMap('WorkHours', Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM'),'666') AS WorkHours,

      ...

       

      I also tried

      ApplyMap('WorkHours', Date(TempDate, 'YYYYMM'),'666') AS WorkHours,

       

      If I hardcode the period it works... and If I see the value "Period" is the same value that I have in the Mapping Table. But only hardcoding the value it works...

       

      ApplyMap('WorkHours', '201302','666') AS WorkHours,

       

       

      Im doing this because I need to show a Chart with the productivity of an employee and because each month has a diferent amount of workable hours... I try this approach but I hear any other idea.

       

      Thx

        • Re: ApplyMap on Master Calendar is not working
          Arjun Arjun

          Hi Rodrigo,

           

          I'm not sure that, this solution will suits ur problem, try this...

           

          ApplyMap('WorkHours',  Month(Date(TempDate,'YYYYMM'),'666')) AS WorkHours,

          • Re: ApplyMap on Master Calendar is not working
            Mohit Sharma

            Try to Ist load through inline Month and Hours

            like this

            WorkHours:

            LOAD * INLINE [

                Month, Hours

                201301, 186

                201302, 152

                201303, 160

                201304, 160

                201305, 176

                201306, 144

                201307, 176

                201308, 168

                201309, 168

                201310, 176

                201311, 168

                201312, 168

            ];

            then

            WorkHr:

            mapping load  

            Date(Date#(Month,'YYYYMM'),'YYYYMM') as MONTH,Hours AS HR

            resident  WorkHours;

            Drop table WorkHours ;

            then in master Calendar applymap on this table

            like this

            Calendar:

            LOAD

                Date(TempDate) AS CalendarDate,

                Date(TempDate,'YYYYMM') AS Period,

                ApplyMap('WorkHr', Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM'),'666') AS WorkHours,

             

            because during mapping one field is primary  and qlikview doent know its format hope it helps

             

            hope it helps

            • Re: ApplyMap on Master Calendar is not working
              Borja Rodriguez

              Hi Rodrigo,

               

              You should first check the result of the expression you are using to pass it wo the Applymap function. In order to to that, load it as a new field and check the internal value using a listbox or tablebox and change the Number Format to Integer to see if you are getting a full date or just an integer with YYYYMM.

               

              LOAD

              ...

              Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM') as Test_Field

              ...

              I'm afraid you are not getting a YYYYMM integer as the Applymap function should receive.

               

              Try with this:

              ApplyMap('WorkHours', year(TempDate) * 100 + num(month(TempDate))   ,'666') AS WorkHours,

               

              Also, for the same results, I think you could join the WorkHours table to the Calendar.

               

              Kind Regards,

               

              Borja

              • Re: ApplyMap on Master Calendar is not working
                Rodrigo Giner

                Sorry for the delay...too busy at work.

                 

                It seems that Date( ) uses full date format DD.MM.YYYY thats why only worked on the first day of each month.

                 

                I concatenate string without the use of Date to make it work:

                 

                ApplyMap('WorkHr', right(TempDate,4) & mid(TempDate,4, 2 ),'666') AS WorkHours,