Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to split the value between two timestamps into 15 minutes, so I have 96 records per day.
I have this table:
ID | TimeStamp_Start | TimeStamp_End | Value |
123456 | 01.03.2020 05:42 | 01.03.2020 06:11 | 567 |
987654 | 01.04.2020 02:57 | 01.02.2020 00:10 | 12577,68 |
I want this table:
ID | Interval_Start | Interval_End | Value |
123456 | 01.03.2020 05:15 | 01.03.2020 05:29 | 0 |
123456 | 01.03.2020 05:30 | 01.03.2020 05:44 | 58,65 |
123456 | 01.03.2020 05:45 | 01.03.2020 05:59 | 293,27 |
I also need a record when the value is 0, so for everyday 96 records.
I really hope someone can help me!
Thanks!
I'm not sure how the Value is calculated here. But see how the Bucket can be created.
tab1:
LOAD *
,Date(Floor(Date#(TimeStamp_Start, 'MM.DD.YYYY hh:mm'))) As Date_Start
,Date(Floor(Date#(TimeStamp_Start, 'MM.DD.YYYY hh:mm'))+15*(IterNo()-1)/(24*60)) As Bucket_Start
While IterNo()<=96
;
LOAD * INLINE [
ID, TimeStamp_Start, TimeStamp_End, Value
123456, 01.03.2020 05:42, 01.03.2020 06:11, 567
987654, 01.04.2020 02:57, 01.05.2020 00:10, "12577,68"
];
Hi,
the "new" Value should be calculated by the minutes between TimeStamp_Start and TimeStamp_End.
Value / (TimeStamp_End-TimeStamp_Start)
Can someone please help me!