Rolling Up

[brindlogcool]

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???

[MayilVahanan]

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

[brindlogcool]

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.

[nagaiank]

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.

[brindlogcool]

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

[brindlogcool]

Thanks Krishnamoorthy,

Above code will work as long as the end date for two different set of users are different. i tried the above load script code it is adding the terr2 time to terr1.Ideally the end will be common for many records.So i am trying to change at the Alignment itself.

[brindlogcool]

I got the solution.

Max of end date group by Emp id and use Apply map. It is working fine as expected . Thanks for all responses.