Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
khag_hs
Contributor II
Contributor II

Get the average of values 5 steps backwards in the script

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:

Nrvalueflag
1100
2110
3120
4100
5110
691
780
870
9100
1030
1120
1210
1311
1470

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

 

Average_Table:

Nraverage
610,6
133,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)

;

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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
Contributor II
Contributor II
Author

it works!

Thank You