Interpolating Time Series Data in Qlikview

[Patreson31]

Hi there,

I was wondering if someone out there had a clever solution in qlikview to interpolate timeseries data points. Currently I have a load of  sensors that take data at random frequency. I'm trying to think of a way in qlikview that i could interpret these points on a half hourly basis? 

Some sensors take readings every minute, whilst others only take 1 reading an hour - making it a bit more tricky.

Attached an example of the dataframe.

Any help or suggestions would be massively appreciated!

[Saravanan_Desingh · Accepted solution]

Can you try this?

tab1:
LOAD *
	 ,Date(Floor(Timestamp#(Sensor_Timestamp,'DD/MM/YYYY hh:mm:ss'))) As Sensor_Date
	 ,Time(Frac(Timestamp#(Sensor_Timestamp,'DD/MM/YYYY hh:mm:ss')),'hh:mm:ss') As Sensor_Time
	 ;	 
LOAD * INLINE [
    Sensor, Sensor_Timestamp, Value
    A, 18/04/2020 14:05:37, 0.02
    A, 18/04/2020 14:45:39, 0.02
    A, 18/04/2020 15:05:40, 0.02
    B, 18/04/2020 13:58:43, 38.09
    B, 18/04/2020 14:58:49, 38.09
];

HrsBucket:
LOAD Time((IterNo()-0.5*IterNo())/24,'hh:mm:ss') As Start
	 ,Time(((IterNo()-0.5*(IterNo()-1))/24)-1/86400,'hh:mm:ss') As End
	 ,Time((IterNo()-0.5*IterNo())/24,'hh:mm:ss') As HrsBucket
AutoGenerate 1
While IterNo()<=48
;
//LOAD * INLINE [
//    Start, End, HrsBucket
//    13:30:00, 13:59:59, 13:30
//    14:00:00, 14:29:59, 14:00
//    14:30:00, 14:59:59, 14:30
//    15:00:00, 15:29:59, 15:00
//];

Inner Join IntervalMatch(Sensor_Time)
LOAD Start, End
Resident HrsBucket;