Below is how to use #GETTABVAL to vary the assumed number of hours per day over time based on plan, location and age. 

LocationCode and PlanCode are effective date array coded fields that indicate in which locations and plans the member has been. The value in each of these arrays is a numeric code as follows.

The LocationCode array contains:

Date	Code
1/01/1980	1
11/17/1990	2
9/3/1999	1
1/01/2012	2

 and the PlanCode array contains:

Date	Code
1/01/1980	1
7/21/1995	2
1/01/2012	3

 

The HoursByPlanLocAge.CSV file might look like this:

Date	Plan	Loc	Age	Value
R	E	E	R	V
1/1/2000	1	0	0	8.1
1/1/2000	1	1	55	8
1/1/2000	1	1	60	7.9
1/1/2000	2	1	0	7.7
1/1/2000	2	1	55	7.6
1/1/2000	2	1	60	7.9
1/1/2000	3	1	0	8.1
1/1/2000	3	1	55	7.7
1/1/2000	3	1	60	7.8
...
1/1/2000	1	2	0	7.8
1/1/2000	1	2	55	7.7
1/1/2000	1	2	60	7.7
...

 

or this:

Date	Plan	Type	Loc	City	Age	Value
R	E	I	E	I	R	V
1/1/2000	1	Salaried	1	NYC	0	8.1
1/1/2000	1	Salaried	1	NYC	55	8
1/1/2000	1	Salaried	1	NYC	60	7.9
1/1/2000	2	Hourly	1	NYC	0	7.7
1/1/2000	2	Hourly	1	NYC	55	7.6
1/1/2000	2	Hourly	1	NYC	60	7.9
1/1/2000	3	Admin	1	NYC	0	8.1
1/1/2000	3	Admin	1	NYC	55	7.7
1/1/2000	3	Admin	1	NYC	60	7.8
...
1/1/2000	1	Salaried	2	LA	0	7.8
1/1/2000	1	Salaried	2	LA	55	7.7
1/1/2000	1	Salaried	2	LA	60	7.7
...

 

Here is what an hours conversion transformation might look like:

D_DT:= #DATE & calculation dates

LOC:= LocationCodes &

PLAN:= PlanCodes &

Age:= D_DT #YEARDIF DateOfBirth &

Rates:= ‘HoursByPlanLocAge’ #GETTABVAL (D_DT, PLAN, LOC, AGE) &

DaysWorked:= 0 #MAX [(1 + #DATE) – DateOfHire] &

NetDaysWorked:= 0 #MPNET DaysWorked &

NetHoursWorked:= NetDaysWorked * Rates &

HoursInYear:= 1 #MPSUM NetHoursWorked &

#IF HoursInYear < 501
#THEN 0
#ELSEIF HoursInYear < 1000
#THEN .5
#ELSE 1 #MIN (HoursInYear / 1800) #ENDIF

If you wanted to freeze the rates in effect at 12/31/2012, but reflect any age, plan and location changes that occur after 12/31/2012, you only need to freeze the date:

; freeze the rates in effect at 12/31/2012, but reflect any age,
; plan and location changes that occur after 12/31/2012

D_DT:= 12/31/2012 #MIN #DATE & ; freeze the calculation dates at 12/31/12

LOC:= LocationCodes &

PLAN:= PlanCodes &

Age:= D_DT #YEARDIF DateOfBirth &

Rates:= ‘HoursByPlanLocAge’ #GETTABVAL (D_DT, PLAN, LOC, AGE) &

DaysWorked:= 0 #MAX [(1 + #DATE) – DateOfHire] &

NetDaysWorked:= 0 #MPNET DaysWorked &

NetHoursWorked:= NetDaysWorked * Rates &

HoursInYear:= 1 #MPSUM NetHoursWorked &

#IF HoursInYear < 501
#THEN 0
#ELSEIF HoursInYear < 1000
#THEN .5 #ELSE 1 #MIN (HoursInYear / 1800) #ENDIF