Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
khag_hs
New 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

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;

View solution in original post

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;

View solution in original post

Highlighted
khag_hs
New Contributor II

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

it works!

Thank You