6 Replies Latest reply: Jul 21, 2012 8:50 PM by Logesh Jayaraman RSS

    Rolling Up

    Logesh Jayaraman

      Hi,

       

      I have two tables Alignment Table and Activity Table.

       

      Alignment Table

       

                EMPID    GEOCODE      STARTDATE        ENDDATE

                  101          TER1            01/01/ 2012         01/31/ 2012     

                  102          TER1            03/01/ 2012         03/31/ 2012     

                  101          TER2            04/01/ 2012         04/22/ 2012     

       

      Activity table

               

               

               EMPID    GEOCODE    ACTIVITY  STARTDATE        ENDDATE           TIME(Days)

                  101          TER1           Meeting    01/10/ 2012         01/15/ 2012            6

                  101          TER1           Leave       01/18/ 2012         01/22/ 2012            5

                  102          TER1            PR          03/01/ 2012         03/20/ 2012           21

                  101          TER2             OT          04/01/ 2012         04/15/ 2012           16

       

       

      I want to display like this-- Add the time and display it on the latest geocode of the empid. Emp 101 has two geocode so i need to add the time of both geocode and display on the latest geocode.

       

       

                       EMPID      GEOCODE     TIME

                          101            TER2           27   

                          102            TER1           21

       

       

      Any idea???

        • Re: Rolling Up
          mayilvahanan ramasamy

          HI,

           

               For this, you use interval match. 

           

              

               You can refer about in reference manual or  below posts:

           

               http://community.qlik.com/message/192808#192808

           

               http://community.qlik.com/message/222133#222133

           

               Hope it helps

            • Re: Rolling Up
              Logesh Jayaraman

              Thanks for your response. I have done the part of interval match to spit the dates. but how to add all the time and display it in the latest GEOCODE for each user.

                • Re: Rolling Up
                  Nagaian Krishnamoorthy

                  If you want to display as straight table, you may use the following settings:

                   

                  Dimension: EMPID

                  Expression 1 for LastGeocode: FirstSortedValue(GEOCODE,-ENDDATE)

                  Expression 2 for TotalTime: Sum(TIME)

                   

                  Or if you want to use the script, you may get the Display data from your Activit table using the script

                   

                  Display:

                  LOAD EMPID

                            ,FirstSortedValue(GEOCODE,-ENDDATE) as LastGEOCODE

                            ,Sum(TIME) as TotalTIME

                            Resident Activity Group By EMPID;

                   

                  Hope this helps.

                    • Re: Rolling Up
                      Logesh Jayaraman

                      Thanks for your response krishnamoorthy, i am trying to change the logic itself since there is no use of having the history. So trying to map it in the alignment itself.Since users are not going to see the hostory.

                       

                       

                      Alignment Table

                       

                                EMPID    GEOCODE      STARTDATE        ENDDATE

                                  101          TER1            01/01/ 2012         01/31/ 2012     

                                  102          TER1            03/01/ 2012         03/31/ 2012     

                                  101          TER2            04/01/ 2012         04/22/ 2012

                       

                      I am trying to do in the load script as below (Updating the Latest GEO CODE for all the rows of the user ).Once aligment is changed like this.i can use the interval match to get the other things mapped easliy.

                       

                      Alignment Table

                       

                                EMPID    GEOCODE      STARTDATE        ENDDATE

                                  101          TER2            01/01/ 2012         01/31/ 2012     

                                  102          TER1            03/01/ 2012         03/31/ 2012     

                                  101          TER2            04/01/ 2012         04/22/ 2012

                      Once again thanks for your response