Create new field in script; new number in each row is based on a varying calculation with different result to each row

[#1]

Appreciate if someone can help:

  - I have a table that I need to create some new fields in the script

  - the numbers (e.g. X1, X2, ...) in each row (e.g. row 1, 2, ...) from each new field (e.g. newfield26, newfield27, ...) has to be created base on a formula that takes reference to two sets of numbers,

  1.  numbers in the 'Points' field (e.g. P1, P2, ....), and

  2.  numbers in a particular field (e.g. M26, M27, ...) in another table

Below I have illustrated how the formula shall look like if these numbers are to be calculated and populated in these new fields.

I have also attached the .qvw I have created this far with the original data excel tables.

The problem is that I am not sure what I shall put there in 'X,' 'Y', 'Z', when creating these new fields.

Much appreciate your help. Thank you.

Refer_table:
Directory;
LOAD Parameter,
      M28,
      M27,
      M26
FROM
[refer table.xls]
(biff, embedded labels, table is Sheet2$);


Points_temp:
Directory;
LOAD Points,
      DateMDY,
      DateSerial
FROM
[original data.xls]
(biff, embedded labels, table is Sheet1$);


Points:
LOAD DateSerial,
      DateMDY,
      Points,
      'X' as newfield26,
      'Y' as newfield27,
      'Z' as newfield28
Resident [Points_temp];    

Drop Table Points_temp;

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

Refer_table:
LOAD Parameter,
  M28, 
     M27, 
     M26
FROM
[comm208647_refer table.xls]
(biff, embedded labels, table is Sheet2$);

Points_temp:
LOAD Points, 
     DateMDY, 
     DateSerial
FROM
[comm208647_original data.xls]
(biff, embedded labels, table is Sheet1$);

JOIN (Points_temp)

LOAD * Resident Refer_table;

Points:
LOAD *,
  rangesum(M26*Points,peek(M26,-1)*peek(Points,-5) ,peek(M26,-2)*peek(Points,-10),peek(M26,-3)*peek(Points,-15),peek(M26,-4)*peek(Points,-20)) as X,
  rangesum(M27*Points,peek(M27,-1)*peek(Points,-5) ,peek(M27,-2)*peek(Points,-10),peek(M27,-3)*peek(Points,-15),peek(M27,-4)*peek(Points,-20)) as Y,
  rangesum(M28*Points,peek(M28,-1)*peek(Points,-5) ,peek(M28,-2)*peek(Points,-10),peek(M28,-3)*peek(Points,-15),peek(M28,-4)*peek(Points,-20)) as Z
Resident [Points_temp]
ORDER By DateSerial, Parameter desc;     

Drop Table Points_temp;

FinalPoints:
Noconcatenate
LOAD * Resident Points Where Parameter = 0;

Drop Table Points;
Drop Field M26,M27,M28, Parameter from FinalPoints;

[#3]

Much appreciate your help Gysbert.

I do not get the correct result initially however a little twist from your great suggestion (& script) gives me what I want! Thank you very much.