topic interpolation/avg/filling for data sets with gaps in QlikView

interpolation/avg/filling for data sets with gaps

Fri, 30 Oct 2015 12:30:06 GMT

Hey all,

I'm facing an issue which hopefully can be solved easily, however I just couldn't. Situation is that I'm having two data sets, one includes values in a 1-minute interval, the other in a 5-minutes interval. Later I'd like to process the values with each other. Meaning I need to fill the empty gaps of the 5-minutes data. First and probably cleanest approach would be to run a interpolation method to fill the empty gaps with the interpolated value. With the given functions like linest_m I struggled ... maybe you can help? Otherwise it also would be great to do the same with the average over the whole record set. I tried with avg(value) and group by but it also didnt work .... last but not least I also would be ok with just filling the empty gaps with the existing values until a new records appears. Meaning following:

record_number	value	interpolation approach 1	avg approach 2	filling approach 3
1	5	5	5	5
2	-	5,4	5	5
3	-	5,8	5	5
4	-	6,2	5	5
5	-	6,6	5	5
6	7	7	5	7
7	-	6,2	5	7
8	-	5,4	5	7
9	-	4,6	5	7
10	-	3,8	5	7
11	3	3	5

So hopefully you can help me out to realize at least one of those approaches (But would love to find solutions for first and second). Thanks a lot!

Best

Henrik

Re: interpolation/avg/filling for data sets with gaps

swuehl — Fri, 30 Oct 2015 16:24:49 GMT

Approach 2 and three can be done easily in QV scripting, approach 1 may need some more work, but should be doable.

Here is script code for 2+3:

INPUT:

LOAD record_number,

value,

[interpolation approach 1],

[avg approach 2],

[filling approach 3]

FROM

[https://community.qlik.com/thread/189778]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *, If(value, value, Peek(app3)) as app3

Resident INPUT;

LEFT JOIN (RESULT)

LOAD Avg(value) as app2

Resident INPUT;

drop table INPUT;

Re: interpolation/avg/filling for data sets with gaps

swuehl — Fri, 30 Oct 2015 16:34:17 GMT

This includes approach 1:

INPUT:

LOAD record_number,

value,

[interpolation approach 1],

[avg approach 2],

[filling approach 3]

FROM

[https://community.qlik.com/thread/189778]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *, If(value, value, Peek(app3)) as app3

Resident INPUT;

LEFT JOIN (RESULT)

LOAD Avg(value) as app2

Resident INPUT;

drop table INPUT;

RESULT2:

LOAD *,

if(value, value, Peek(tmp)) as tmp,

if(value, value, Peek(app1)-(peek(tmp)-app3)/5) as app1

Resident RESULT

Order by record_number desc;

drop table RESULT;

drop field tmp;

Re: interpolation/avg/filling for data sets with gaps

Colin-Albert — Fri, 30 Oct 2015 16:51:33 GMT

This post may help Generating Missing Data In QlikView

https://community.qlik.com/docs/DOC-3786

Re: interpolation/avg/filling for data sets with gaps

Wed, 18 Nov 2015 08:55:07 GMT

Hey swuehl,

thanks a lot for that reply. That worked for me!

record_number	value	interpolation approach 1	avg approach 2	filling approach 3
1	5	5	5	5
2	-	5,4	5	5
3	-	5,8	5	5
4	-	6,2	5	5
5	-	6,6	5	5
6	7	7	5	7
7	-	6,2	5	7
8	-	5,4	5	7
9	-	4,6	5	7
10	-	3,8	5	7
11	3	3	5

record_number	value	interpolation approach 1	avg approach 2	filling approach 3
1	5	5	5	5
2	-	5,4	5	5
3	-	5,8	5	5
4	-	6,2	5	5
5	-	6,6	5	5
6	7	7	5	7
7	-	6,2	5	7
8	-	5,4	5	7
9	-	4,6	5	7
10	-	3,8	5	7
11	3	3	5

record_number	value	interpolation approach 1	avg approach 2	filling approach 3
1	5	5	5	5
2	-	5,4	5	5
3	-	5,8	5	5
4	-	6,2	5	5
5	-	6,6	5	5
6	7	7	5	7
7	-	6,2	5	7
8	-	5,4	5	7
9	-	4,6	5	7
10	-	3,8	5	7
11	3	3	5