• Recent
    • Tags
    • Popular
    • Register
    • Login

    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.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    Script to delete problematic datapoint-values like NaN or infinte from the database

    User help
    1
    1
    184
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • S
      sasa
      last edited by

      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:

      1. loop through all datapoints
      2. 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
      
      1. use the informations "datapointid" and "ts" to delete the values
      2. 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

      1 Reply Last reply Reply Quote 0
      • First post
        Last post