Hi everyone,
I want to process the delta which is null in the middle of a time series by taking the next delta after the null to divide to the (count of null + 1)
Here is the data:
| time | id | value | delta |
| 01/02/2022 | 123 | 12 | |
| 02/02/2022 | 123 | 15 | 3 |
| 03/02/2022 | 123 | 20 | 5 |
| 04/02/2022 | 123 | | |
| 05/02/2022 | 123 | | |
| 06/02/2022 | 123 | | |
| 07/02/2022 | 123 | 60 | 40 |
| 08/02/2022 | 123 | 60 | 0 |
| 09/02/2022 | 123 | | |
| 10/02/2022 | 123 | | |
| 01/02/2022 | 145 | 20 | |
| 02/02/2022 | 145 | 50 | 30 |
| 03/02/2022 | 145 | 70 | 20 |
| 04/02/2022 | 145 | 100 | 30 |
| 05/02/2022 | 145 | | |
| 06/02/2022 | 145 | | |
| 07/02/2022 | 145 | 190 | 90 |
| 08/02/2022 | 145 | | |
| 09/02/2022 | 145 | | |
| 10/02/2022 | 145 | | |
| 01/02/2022 | 987 | 50 | |
| 02/02/2022 | 987 | 100 | 50 |
| 03/02/2022 | 987 | 160 | 60 |
| 04/02/2022 | 987 | 200 | 40 |
| 05/02/2022 | 987 | 230 | 30 |
| 06/02/2022 | 987 | 280 | 50 |
| 07/02/2022 | 987 | 360 | 80 |
| 08/02/2022 | 987 | 420 | 60 |
| 09/02/2022 | 987 | 500 | 80 |
| 10/02/2022 | 987 | 550 | 50 |
Here is when I untable
| time | 123 | 145 | 987 |
| 01/02/2022 | 0 | 0 | 0 |
| 02/02/2022 | 3 | 30 | 50 |
| 03/02/2022 | 5 | 20 | 60 |
| 04/02/2022 | 10 | 30 | 40 |
| 05/02/2022 | 10 | 30 | 30 |
| 06/02/2022 | 10 | 30 | 50 |
| 07/02/2022 | 10 | 30 | 80 |
| 08/02/2022 | 0 | 0 | 60 |
| 09/02/2022 | | 0 | 80 |
| 10/02/2022 | | 0 | 50 |
As in the table, there is 1 record in 08/02/2022 when delta is 0. For other data where it doesn't have data, it is null --> ok
But for id=145, delta from 08 to 10/02 should be empty.
So that when I calculate the avg(delta), it will not effect.
So my question is how to distinguish the zero and the null in this case?
Thanks!
