Interpolating

web

Hi Experts
I have created a report that is looking at a table called discount factor. This table has discount factor and commutation factors corresponding to each age. The reports looks at the age and pick the corresponding discount factor and commutation factor.
Now where you have a decimal age i.e an age between to ages which does not have a corresponding discount and commutation factors in the discount factor table, we want to interpolate.
Here is the code that picks up the discount and commutation factors when the ages are whole numbers or they correspond to a discount factor and commutation factor in the table.

Dis.SETRANGE(Age,ageOfEmp);
IF Dis.FIND('-') THEN
BEGIN
   DF := Dis."Discount Factor";
   DFpre08 := Dis."Discount Factor Pr08";
   CF := Dis."Commutation Factor";
   CFpre08 := Dis."Commutation Factor Pr08";
END ELSE
BEGIN 

   ERROR('Unable to get Discount Factors please ensure they are entered in the database');

END;

// Updating DF and CF on Employee Table.
Employee."Discount Factor" := DF;
Employee."Discount Factor Pr08" := DFpre08;
Employee."Commutation Factor" := CF;
Employee."Commutation Factor Pr08" := CFpre08;
Employee.MODIFY();
{ End of code for search DF and update Employee table. }

how can i do the interpolation on the above?
Here is how the interpolation should be:

To  calculate discount facor
[(B-A * Additional months to A)/12] + A

To calculate commutation factor
A-[((A-B)*Additional months to B)/12]

How can i use the Above interpolation formula to fit in the code above/ Can someone help me please?

BBlue

Hello,

I assume the table Discount Factor has "Age" as primary key... In this case you could try:

IF Dis.GET(ageOfEmp) THEN BEGIN
  DF := Dis."Discount Factor"; 
  DFpre08 := Dis."Discount Factor Pr08"; 
  CF := Dis."Commutation Factor"; 
  CFpre08 := Dis."Commutation Factor Pr08"; 
END ELSE IF (Dis.GET(ROUND(ageOfEmp, 1, '<')) AND Dis.GET(ROUND(ageOfEmp, 1, '>'))) THEN BEGIN  //between 2 consecutive values stored in the table
  DF := ... 
  CF := ...  // implement your interpolation
END else ERROR('Unable to get Discount Factors');

Hope I understood you and the above will somewhat help you... if not please give us some more details like what are A and B?

Xypher

I'm curious too on which values you're referring to as A and B.

Is A the next available whole number age? or is it the next available age CF/DF? And B would be previous of whichever?

As far as the code provided by BBlue, I would probably use a better age range record finder.

ex...

IF NOT Dis.GET(ageOfEmp) THEN BEGIN
  Dis.RESET;
  Dis.SETFILTER(Age,'>=%1',(ageOfEmp DIV 1)); //Or use ROUND if you like
  IF NOT Dis.FINDSET THEN
    ERROR('Could not find next available age.');

  IF Dis.Age < ageOfEmp THEN BEGIN
    //Found whole number of current age
    IF Dis.NEXT = 0 THEN
      ERROR('Could not find next available age.');
    //Found next available age
  END ELSE BEGIN
    //Found next available age
    IF Dis.NEXT(-1) = 0 THEN
      ERROR('Could not find record of current/previous age.'); 
    //Found previous age
  END;
END ELSE BEGIN
  //ageOfEmp was a whole number & found etc..
END;

//Do interpolating calculations

That way you can at least get the previous and next ages if they aren't side by side.

kolaboy

The Original poster told me by MSN that
in A- B , A is the next DF and B is the Previous and
in B-A B is the next CF and A is the previous
I hope thgis will help you to assist him.

How can you fity in the interpolation formula in?

BBlue

And what does it mean "Additional months to A" and "Additional months to B"? It's somewhat the difference in months between ageOfEmp and the first age below (if exists) ?

kolaboy

Additional Months to A means the number of months added to A which makes it more than A but less than B
Example A= 25 and B=26 and the age between them which should be interpolated is 25.5 and 0.5 is the addition months to A which is 6 months.
I hope this is clear?

web

Thanks Kolaboy, that is exactly what it means.

BBlue

Hello,

I thought a bit more at this and I wrote some code which will hopefully help you. Of course, I believe it should be refined and maybe modified to serve your purpose.
First of all, I started with the assumption that the Discount Factor table has "Age" as a primary key, you dind't mentioned that, but this is the structure I would create such a table.
I calculated "CF" and "DF" based on the exact formulas you provided, you should check again... and "AgeDifference" is the difference from the age required to interpolate and the nearest lower age - multiplied by 12 gives you the difference in months.
dis_below and dis_above are rec variables for Discount Factor table which I used to search for the nearest lower and upper values of the age that will be interpolated.
As for "Discount Factor Pr08" and "Commutation Factor Pr08" - I believe it can be calculated the same as DF and CF.

IF AgeOfEmp MOD 1 = 0 THEN BEGIN
  IF dis.GET(AgeOfEmp) THEN BEGIN
    df := dis."Discount Factor";
    cf := dis."Commutation Factor";
  END ELSE BEGIN
    dis_below.RESET;
    dis_below.SETFILTER(Age,'<%1',AgeOfEmp);
    IF dis_below.FINDLAST THEN BEGIN
      dis_above.RESET;
      dis_above.SETFILTER(Age,'>%1',AgeOfEmp);
      IF dis_above.FINDFIRST THEN BEGIN
        AgeDifference := AgeOfEmp - dis_below.Age;
        df := ((dis_above."Discount Factor" - dis_below."Discount Factor" * 12 * AgeDifference) / 12) +
                                              dis_below."Discount Factor";
        cf := dis_above."Commutation Factor" - (((dis_above."Commutation Factor" -
                                             dis_below."Commutation Factor") * 12 * AgeDifference) / 12);
      END ELSE ERROR('There is no upper value in the table');
    END ELSE ERROR('There is no lower value in the table');
  END;
END ELSE BEGIN
  dis_below.RESET;
  dis_below.SETFILTER(Age,'<=%1',ROUND(AgeOfEmp, 1, '<'));
  IF dis_below.FINDLAST THEN BEGIN
    dis_above.RESET;
    dis_above.SETFILTER(Age,'>=%1',ROUND(AgeOfEmp, 1, '>'));
    IF dis_above.FINDFIRST THEN BEGIN
      AgeDifference := AgeOfEmp - dis_below.Age;
      df := ((dis_above."Discount Factor" - dis_below."Discount Factor" * 12 * AgeDifference) / 12) + dis_below."Discount Factor";
      cf := dis_above."Commutation Factor" - (((dis_above."Commutation Factor" -
                                             dis_below."Commutation Factor") * 12 * AgeDifference) / 12);
    END ELSE ERROR('There is no upper value in the table')
  END ELSE ERROR('There is no lower value in the table');
END;

Good luck!

Xypher

BBlue, unfortunately it is rather impossible to use SETFILTER(Field,'<%1',Var)

(Ah nm, I didn't notice you used Record.*FINDLAST*) - Because essentially even the first record would be less than 'Var'. Best method to use would be to find the first record using '>%1' filter and then use NEXT(-1) to get the record before.

[edit: Oh and there's really no need to round the age in the filter. Ex: ageOfEmp = 53.6, Age: 54 is greater than ageOfEmp whether it is 53.6 or 53]

kolaboy

How can you fit in you suugestion in BBlue's code above..Can you edit the code let me see, because i used BBlue's code but it not working.
Thanks

BBlue

Xypher,

You can use indeed setfilter '>%1' to find the next record and then NEXT(-1) to find the previous, that's just another way to do it... as for the ROUND, you are right, you don't necessary need to use it, but there is nothing wrong with that... I wonder what the real problem is :-k

kolaboy and/or web ,

What exactly does not work? How do you use this report? Don't you enter an ageOfEmp from RequestForm and the report calculates the CF and DF (if not found in table by interpolating)? I did't tested on real data and I used your exact formulas, as posted here... I see this report as a batch type with no data items and that just calculates the CF and DF you need. Please correct me if I am wrong.
We do want to help you, but if not providing more info we can just assume some details...

web

I am using an Employee table as my dataitem and i don't enter ageOfEmp from RequestForm. The age is calculated using the employee date of birth which is subtracted from TODAY's date which is entered in a request form on the option tab and it calculate's the employee's age. This result is compare to the discount factor table and if coincides with an age say 65, it picks the corresponding DF and CF, but where it does not and it falls between two ages say 65 and 66 i.e 65.5, we interpolate. this interpolation is what it am having problems with( how can it be achieved)

// interpolation starts here
IF Dis.FIND('-') THEN
BEGIN
IF ageOfEmp MOD 1 = 0 THEN BEGIN
    DF := Dis."Discount Factor";
    DFpre08 := Dis."Discount Factor Pr08";
    CF := Dis."Commutation Factor";
    CFpre08 := Dis."Commutation Factor Pr08";
  END ELSE BEGIN
    dis_below.RESET;
    dis_below.SETFILTER(Age,'<%1',ageOfEmp);
    IF dis_below.FIND('+') THEN BEGIN
      dis_above.RESET;
      dis_above.SETFILTER(Age,'>%1',ageOfEmp);
      IF dis_above.FIND('-') THEN BEGIN
        AgeDifference := ageOfEmp - dis_below.Age;
        DF := ((dis_above."Discount Factor" - dis_below."Discount Factor" * 12 * AgeDifference) / 12) +
                                              dis_below."Discount Factor";
        CF := dis_above."Commutation Factor" - (((dis_above."Commutation Factor" -
                                             dis_below."Commutation Factor") * 12 * AgeDifference) / 12);
      END ELSE ERROR('There is no upper value in the table');
    END ELSE ERROR('There is no lower value in the table');
  END;
END ELSE BEGIN
  dis_below.RESET;
  dis_below.SETFILTER(Age,'<=%1',ROUND(ageOfEmp, 1, '<'));
  IF dis_below.FIND('+') THEN BEGIN
    dis_above.RESET;
    dis_above.SETFILTER(Age,'>=%1',ROUND(ageOfEmp, 1, '>'));
    IF dis_above.FIND('-') THEN BEGIN
      AgeDifference := ageOfEmp - dis_below.Age;
      DF := ((dis_above."Discount Factor" - dis_below."Discount Factor" * 12 * AgeDifference) / 12) + dis_below."Discount Factor";
      CF := dis_above."Commutation Factor" - (((dis_above."Commutation Factor" -
                                             dis_below."Commutation Factor") * 12 * AgeDifference) / 12);
    END ELSE ERROR('There is no upper value in the table')
  END ELSE ERROR('There is no lower value in the table');
END;
//Interpolation ends here

Here is BBLUE's code it used to test but not working. I am using 4.0 that is why it used FIND('-') instead of FINDFIRST. We are not making used of DFpre08 and CFpre08. We are considering two periods, before 2008 and after 2007.

BBLUE can check what i am not doing right?
Thanks

BBlue

Hello,

I assume that "Age" is not the primary key of your "Discount Factor" table as I believe you do a SETRANGE instead of a GET, just before the code mentioned:

Dis.SETRANGE(Age,ageOfEmp);

Am I wrong? In this case you don't need the

IF ageOfEmp MOD 1 = 0 THEN BEGIN ....

... this was for the GET not give you an error... You should redesign the code

By the way? How do you calculate the age? Are you using:

ageOfEmp := (TODAY - "Birth Date")/365;

I don't know... put messages during the code, see where and what does not behave correctly...

Xypher

web,

I have some small suggestions, which although may not provide a solution hopefully it might get you working in the right direction.

(Oh and.. I don't think it was too difficult for my company but, if I may suggest, upgrade to at least 4.0 SP1 so you not only can use new, more efficient, features like: Findfirst, Findlast, Findset as well as others.)

Dis.RESET;
Dis.SETFILTER(Age,'%1',ageOfEmp);

IF Dis.FIND('-') THEN BEGIN
  DF := Dis."Discount Factor";
  CF := Dis."Commutation Factor";
END ELSE BEGIN
  Dis.SETFILTER(Age,'>%1',ageOfEmp);

  IF NOT Dis.FIND('-') THEN
    ERROR('Upper bound value could not be found.');

  UBoundDF := Dis."Discount Factor";
  UBoundCF := Dis."Commutation Factor";

  Dis.SETFILTER(Age,'<%1',ageOfEmp);

  IF NOT Dis.FIND('+') THEN
    ERROR('Lower bound value could not be found.');

  LBoundDF := Dis."Discount Factor";
  LBoundCF := Dis."Commutation Factor";
  AgeDiff := ageOfEmp - Dis.Age;
  DF := ((UBoundDF - (LBoundDF * (AgeDiff * 12))) / 12) + LBoundDF;
  CF := UBoundCF - (((UBoundCF - LBoundCF) * (AgeDiff * 12)) / 12);
END;

For your case, even though you probably don't have a huge table of employees, it would be better practice to use variables to store the small data rather than making reference to 3 record variables connecting to the same table. (Dis, dis_below, dis_above)

The main difference between my code and web's/BBlue's is that you two have a section of code that is shown twice. If ageOfEmp is a whole number and not found in the table, the same code to find the median DF/CF of a decimal age is used.

Xypher

web,

I have some small suggestions, which although may not provide a solution hopefully it might get you working in the right direction.

(Oh and.. I don't think it was too difficult for my company but, if I may suggest, upgrade to at least 4.0 SP1 so you not only can use new, more efficient, features like: Findfirst, Findlast, Findset as well as others.)

Dis.RESET;
Dis.SETFILTER(Age,'%1',ageOfEmp);

IF Dis.FIND('-') THEN BEGIN
  DF := Dis."Discount Factor";
  CF := Dis."Commutation Factor";
END ELSE BEGIN
  Dis.SETFILTER(Age,'>%1',ageOfEmp);

  IF NOT Dis.FIND('-') THEN
    ERROR('Upper bound value could not be found.');

  UBoundDF := Dis."Discount Factor";
  UBoundCF := Dis."Commutation Factor";

  Dis.SETFILTER(Age,'<%1',ageOfEmp);

  IF NOT Dis.FIND('+') THEN
    ERROR('Lower bound value could not be found.');

  LBoundDF := Dis."Discount Factor";
  LBoundCF := Dis."Commutation Factor";
  AgeDiff := ageOfEmp - Dis.Age;
  DF := ((UBoundDF - (LBoundDF * (AgeDiff * 12))) / 12) + LBoundDF;
  CF := UBoundCF - (((UBoundCF - LBoundCF) * (AgeDiff * 12)) / 12);
END;

For your case, even though you probably don't have a huge table of employees, it would be better practice to use variables to store the small data rather than making reference to 3 record variables connected to the same table. (Dis, dis_below, dis_above)

The main difference between my code and web's/BBlue's is that you both have two sections of code which are identical. If ageOfEmp is a whole number and not found in the table, the same code to find the median DF/CF of a decimal age is used.