Get the average of values 5 steps backwards in the script

[khag_hs]

I want to build an average 5 steps backwards of a field value with a flag in another column and load the result in a separate table.

For example

Table1:

Nr	value	flag
1	10	0
2	11	0
3	12	0
4	10	0
5	11	0
6	9	1
7	8	0
8	7	0
9	10	0
10	3	0
11	2	0
12	1	0
13	1	1
14	7	0

I’m looking for the average 5 steps backwards from line 6 and line 13.

Average_Table:

Nr	average
6	10,6
13	3,4

I need the Average_Table in the script, not in the layout area.

I try to do it in this way, but the result is not what I want to get:

//******************************************************************* 

Tab1:
LOAD * INLINE [
Nr, value, flag
1, 10, 0
2, 11, 0
3, 12, 0
4, 10, 0
5, 11, 0
6, 9, 1
7, 8, 0
8, 7, 0
9, 10, 0
10, 3, 0
11, 2, 0
12,  1,   0
13,  1,   1
14,  7,   0
];

Tab2:
load *,
rowno(),
RecNo()
resident Tab1;

average_tab:
load
avg(value) as Average,
(FieldIndex('flag','1')) as Index
resident Tab2

where
RecNo()<=(FieldIndex('flag','1'))
and
RecNo()>=(FieldIndex('flag','1')-5)

;

[sunny_talwar · Accepted solution]

May be this

Tab1:

LOAD * INLINE [

Nr, value, flag

1, 10, 0

2, 11, 0

3, 12, 0

4, 10, 0

5, 11, 0

6, 9, 1

7, 8, 0

8, 7, 0

9, 10, 0

10, 3, 0

11, 2, 0

12,  1,  0

13,  1,  1

14,  7,  0

];

average_tab_tmp:

LOAD Nr,

value

Resident Tab1

Where flag = 1;

FOR i = 1 to 4

Left Join (average_tab_tmp)

LOAD Nr + $(i) as Nr,

value as value$(i)

Resident Tab1;

NEXT i

average_tab:

LOAD Nr,

RangeAvg(value, value1, value2, value3, value4) as Average_value

Resident average_tab_tmp;

DROP Table average_tab_tmp;

[khag_hs]

it works!

Thank You