Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
khag_hs
New Contributor

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

Re: Get the average of values 5 steps backwards in the script

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;

2 Replies

Re: Get the average of values 5 steps backwards in the script

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
New Contributor

Re: Get the average of values 5 steps backwards in the script

it works!

Thank You

Community Browser