Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Script to delete problematic datapoint-values like NaN or infinte from the database
-
Hi together,
for some unknown reasons the database has "NaN"-entries. This entries make problems with the "sum" and also with the "integral" calculations, they are simply wrong. The pattern why it happens is not clear for me, but I will check further.What I realized is, that a bunch of datapoints has the NaN-entry at the same timestamp.
Now my question. How can I clean this up?
The available backend-scripts are not showing such examples, but it looks like this will be the best way. API did offer some methods, but it is not documented that good that I can find a way.
Ideas for the script:
- loop through all datapoints
- query values of the first datapoint and search for NaN-values
This example query the values via sql-console, but "delete" command is not allowed here.
SELECT * FROM POINTVALUES WHERE DATAPOINTID = 28 AND nullif(POINTVALUE, 'NaN') is null--output
ID DATAPOINTID DATATYPE POINTVALUE TS 14134 28 3 NaN 1700760554775 19447 28 3 NaN 1700762775326 20933 28 3 NaN 1700763381424 201309 28 3 NaN 1711605424899- use the informations "datapointid" and "ts" to delete the values
- go on with next datapoint.
I want to use it also for cleaning values that are infinite or out of set value ranges (extreme), but this is an second requirement.
Are there some best practices and examples available?
Regards
Sasa