Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
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
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.
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.
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
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.
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.