Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have here a script question.
The first 3 columns are input values.
Column Gradient needs to be expanded with column Output as results of script calculation.
Column Gradient is based on the sum of Distance, always <= 500 meter.
Let's consider ID = 5. The sum of Distance is equal 350 meter and so all cells DI 1-5 is equal to 12.
Now for ID = 17.
Gradient = 20. The sum of Distance for ID = 17, 16, 15, 14, 13 = 476 and agree with the requirement that sum < 500. So, the cells must be filled with 20 for ID = 17, 16, 15, 14, 13.
Table:
LOAD RowNo() as ID, * inline
[
Distance, Gradient
0
100
201
305
350,12
550
600
700,10.5
982
1199,15
1400
1485
1535
1596
1699
1800
2011,20
2239
2399
2600
];
Table2:
NoConcatenate LOAD *,
if (previous(Gradient) > 0, Previous(Gradient)) as Output
Resident Table Order by ID desc;
Could you please provide me the solution? I think you need peek function with Rangesum but I do not know how to solve this.
Best regards,
Cornelis
Hi,
one solution might be:
Table:
LOAD RowNo() as ID, * Inline [
Distance, Gradient
0
100
201
305
350,12
550
600
700,10.5
982
1199,15
1400
1485
1535
1596
1699
1800
2011,20
2239
2399
2600
];
Join
LOAD ID,
If(IsNum(Gradient),Distance,Peek(LastDistWithGrad)) as LastDistWithGrad,
Alt(Gradient,If(Peek(LastDistWithGrad)-Distance<=500,Peek(Output))) as Output
Resident Table Order by ID desc;
DROP Field LastDistWithGrad;
hope this helps
regards
Marco
Hi,
one solution might be:
Table:
LOAD RowNo() as ID, * Inline [
Distance, Gradient
0
100
201
305
350,12
550
600
700,10.5
982
1199,15
1400
1485
1535
1596
1699
1800
2011,20
2239
2399
2600
];
Join
LOAD ID,
If(IsNum(Gradient),Distance,Peek(LastDistWithGrad)) as LastDistWithGrad,
Alt(Gradient,If(Peek(LastDistWithGrad)-Distance<=500,Peek(Output))) as Output
Resident Table Order by ID desc;
DROP Field LastDistWithGrad;
hope this helps
regards
Marco
Dear Marco,
This is very good. I didn't realize that Alt function is a form of extended IF. A good learning point
Best regards,
Cornelis