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