topic Rolling Up in QlikView

Rolling Up

brindlogcool — Fri, 20 Jul 2012 16:38:51 GMT

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 — Fri, 20 Jul 2012 17:44:25 GMT

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

brindlogcool — Fri, 20 Jul 2012 18:02:45 GMT

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

nagaiank — Sat, 21 Jul 2012 00:47:57 GMT

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

brindlogcool — Sat, 21 Jul 2012 23:50:10 GMT

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

Re: Rolling Up

brindlogcool — Sun, 22 Jul 2012 00:15:57 GMT

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.

Re: Rolling Up

brindlogcool — Sun, 22 Jul 2012 00:50:23 GMT

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.