Calculations with values from array

Dashboard
, ,
1

Hello,

I have a problem with two arrays. I want to calculate with two values from the arrays but get stuck when merging them.
I want to put the values of round and max in relation ( round*100/max ).

image
image259Ă—641 14.3 KB

[
    {
        "id": "fba9ba0d3c274116",
        "type": "inject",
        "z": "02fda711f61a8cbb",
        "name": "Select * from db1",
        "props": [
            {
                "p": "query",
                "v": "SELECT ROUND(DIFFERENCE(max(VALUE_1))*100)/100 FROM mqtt_consumer WHERE (topic='tele/tasmota_1/SENSOR') and (time >= now() - 3d) Group by time(1d)",
                "vt": "str"
            }
        ],
        "repeat": "600",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "x": 210,
        "y": 560,
        "wires": [
            [
                "677ff0330cc21702"
            ]
        ]
    },
    {
        "id": "677ff0330cc21702",
        "type": "influxdb in",
        "z": "02fda711f61a8cbb",
        "influxdb": "fb1c96812bb5b413",
        "name": "DB1",
        "query": "",
        "rawOutput": false,
        "precision": "",
        "retentionPolicy": "",
        "org": "organisation",
        "x": 410,
        "y": 560,
        "wires": [
            [
                "47a1e6dca0d0efc1",
                "e4fe26a503dbb0e6"
            ]
        ]
    },
    {
        "id": "024576c1e9084aad",
        "type": "inject",
        "z": "02fda711f61a8cbb",
        "name": "Select * from db2",
        "props": [
            {
                "p": "query",
                "v": "SELECT max(\"value\") from mqtt_consumer WHERE (topic='tele/tasmota_2/SENSOR') and (time >= now() - 3d) Group by time(1d)",
                "vt": "str"
            }
        ],
        "repeat": "300",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 220,
        "y": 640,
        "wires": [
            [
                "733cb51e55a28d7e"
            ]
        ]
    },
    {
        "id": "733cb51e55a28d7e",
        "type": "influxdb in",
        "z": "02fda711f61a8cbb",
        "influxdb": "69cee814.fcbdb8",
        "name": "DB2",
        "query": "",
        "rawOutput": false,
        "precision": "",
        "retentionPolicy": "",
        "org": "organisation",
        "x": 420,
        "y": 640,
        "wires": [
            [
                "47a1e6dca0d0efc1",
                "e4fe26a503dbb0e6"
            ]
        ]
    },
    {
        "id": "fb1c96812bb5b413",
        "type": "influxdb",
        "hostname": "192.168.1.10",
        "port": "8086",
        "protocol": "http",
        "database": "db1",
        "name": "db1",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "1.x",
        "url": "http://localhost:8086",
        "rejectUnauthorized": true
    },
    {
        "id": "69cee814.fcbdb8",
        "type": "influxdb",
        "hostname": "192.168.1.20",
        "port": "8086",
        "protocol": "http",
        "database": "db2",
        "name": "",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "1.x",
        "url": "http://localhost:8086",
        "rejectUnauthorized": false
    }
]
2

The flow you posted is just two flows doing Influx queried. What have you done to join the data?

You should take the data you get from the two Influx database nodes and save that in two inject nodes. Attach them to the rest of your flow and provide that so people can see what you are doing.

This way people (who are volunteering their time to help you) won't have to manually create the data you are using and will be able to help you.

3

You can use a join node to join the 2 messages and then merge them based on the value of the key "time" like so:

grafik
grafik1261Ă—103 8.94 KB

[{"id":"0620fcd89f2c0203","type":"inject","z":"21423082d584a949","name":"max","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"time\":\"2024-08-03T00:00:00.000Z\",\"max\":3728},{\"time\":\"2024-08-04T00:00:00.000Z\",\"max\":2514},{\"time\":\"2024-08-05T00:00:00.000Z\",\"max\":3447},{\"time\":\"2024-08-06T00:00:00.000Z\",\"max\":3868}]","payloadType":"json","x":370,"y":160,"wires":[["e3d0f14894937be2"]]},{"id":"517d61c64bee0ace","type":"inject","z":"21423082d584a949","name":"round","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"time\":\"2024-08-03T00:00:00.000Z\",\"round\":0.98},{\"time\":\"2024-08-04T00:00:00.000Z\",\"round\":0.62},{\"time\":\"2024-08-05T00:00:00.000Z\",\"round\":0.97},{\"time\":\"2024-08-06T00:00:00.000Z\",\"round\":0.54}]","payloadType":"json","x":370,"y":200,"wires":[["e3d0f14894937be2"]]},{"id":"e3d0f14894937be2","type":"join","z":"21423082d584a949","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":180,"wires":[["4418ab7a7f42d344"]]},{"id":"becf86c00959111a","type":"debug","z":"21423082d584a949","name":"debug 444","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1410,"y":180,"wires":[]},{"id":"4418ab7a7f42d344","type":"function","z":"21423082d584a949","name":"merge","func":"//https://stackoverflow.com/a/46849389\nlet arr3 = msg.payload[0].map((item, i) => Object.assign({}, item, msg.payload[1][i]));\nmsg.payload = arr3\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":750,"y":180,"wires":[["b6d3d224da63ae31"]]},{"id":"b6d3d224da63ae31","type":"split","z":"21423082d584a949","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":910,"y":180,"wires":[["0986e9cb88ea2b78"]]},{"id":"0986e9cb88ea2b78","type":"function","z":"21423082d584a949","name":"do math","func":"const round = msg.payload.round\nconst max = msg.payload.max\n\nconst value = round*100/max\nmsg.payload.value = value\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1080,"y":180,"wires":[["b9fdf8eeec7c2e3f"]]},{"id":"b9fdf8eeec7c2e3f","type":"join","z":"21423082d584a949","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":"false","timeout":"","count":"","reduceRight":false,"x":1230,"y":180,"wires":[["becf86c00959111a"]]}]
1 Like
4

Surely your example merges on the index of the array not the key "time"?
It just so happens that the key "time" are in the correct index of the arrays.

This example merges on the key "time".

[{"id":"0620fcd89f2c0203","type":"inject","z":"d1395164b4eec73e","name":"max","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"max","payload":"[{\"time\":\"2024-08-03T00:00:00.000Z\",\"max\":3728},{\"time\":\"2024-08-04T00:00:00.000Z\",\"max\":2514},{\"time\":\"2024-08-05T00:00:00.000Z\",\"max\":3447},{\"time\":\"2024-08-06T00:00:00.000Z\",\"max\":3868}]","payloadType":"json","x":350,"y":100,"wires":[["e3d0f14894937be2"]]},{"id":"e3d0f14894937be2","type":"join","z":"d1395164b4eec73e","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":470,"y":120,"wires":[["803d0e86db121a93"]]},{"id":"517d61c64bee0ace","type":"inject","z":"d1395164b4eec73e","name":"round","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"round","payload":"[{\"time\":\"2024-08-03T00:00:00.000Z\",\"round\":0.98},{\"time\":\"2024-08-04T00:00:00.000Z\",\"round\":0.62},{\"time\":\"2024-08-05T00:00:00.000Z\",\"round\":0.97},{\"time\":\"2024-08-06T00:00:00.000Z\",\"round\":0.54}]","payloadType":"json","x":350,"y":140,"wires":[["e3d0f14894937be2"]]},{"id":"803d0e86db121a93","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.round@$round_obj.(\t    $max_obj := $$.payload.max[$.time = $round_obj.time];\t    $max_obj.time = $round_obj.time ? \t        {\t        \"time\": $round_obj.time,\t        \"round\":$round_obj.round,\t        \"max\": $max_obj.max,\t        \"calc\": ($round_obj.round*100)/$max_obj.max\t        }\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":160,"wires":[["becf86c00959111a"]]},{"id":"becf86c00959111a","type":"debug","z":"d1395164b4eec73e","name":"debug 444","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":120,"wires":[]}]```
2 Likes
5

You're right of course, I didn't include my join function because I was sure it was the wrong way.

6

Thank you, you've put me on the right track. I've made it now.

7

Yes, its based on the index :see_no_evil:
Typed to fast... so you must sort the messages prior of merging and hope for the best xD

8

Is it not possible to do the join and calculation in the database query?

9

Why have you got the data in two databases? It would be better to put them in the same database.

This will not affect the problem here though, as it is not possible to combine data from two measurements in Influxdb 1.x. It is possible in 2.x using Flux, but it is not straightforward even then.

10

@polareisbaer I would also add that joining as array may cause issues. If the influx queries arrive in reverse order this will probably skew your calculation. It may be better to join as key value object, or do you influx queries in series and move the payloads to holding properties.

11

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.