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