Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
This post may help Generating Missing Data In QlikView
Hey swuehl,
thanks a lot for that reply. That worked for me!