# Calculations with values from array

**URL:** <https://discourse.nodered.org/t/calculations-with-values-from-array/90148>\
**Category:** Dashboard\
**Tags:** database, influxdb, dashboard-2\
**Created:** [13 August 2024 09:33 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148 "2024-08-13T09:33:22Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![polareisbaer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/polareisbaer/32/84274_2.png) [@polareisbaer](https://discourse.nodered.org/u/polareisbaer)\
**Post date:** [13 August 2024 09:33 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/1 "2024-08-13T09:33:22Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/a/5a04190cd6539b723c47d2f3bef0ad33997168ec.png)

```auto
[
    {
        "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
    }
]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [13 August 2024 09:51 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/2 "2024-08-13T09:51:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kitori](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kitori](https://discourse.nodered.org/u/kitori)\
**Post date:** [13 August 2024 10:00 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/3 "2024-08-13T10:00:59Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7b57e720bdb13ef88a2120ee6e1c59937e415e02.png)

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [13 August 2024 11:39 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/4 "2024-08-13T11:39:24Z")

</div>

> [@kitori](#):
>
> merge them based on the value of the key "time"

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".

````auto
[{"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":[]}]```
````

---

<div class="post-metadata">

**Author:** ![polareisbaer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/polareisbaer/32/84274_2.png) [@polareisbaer](https://discourse.nodered.org/u/polareisbaer)\
**Post date:** [13 August 2024 12:00 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/5 "2024-08-13T12:00:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![polareisbaer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/polareisbaer/32/84274_2.png) [@polareisbaer](https://discourse.nodered.org/u/polareisbaer)\
**Post date:** [13 August 2024 12:01 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/6 "2024-08-13T12:01:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kitori](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kitori](https://discourse.nodered.org/u/kitori)\
**Post date:** [13 August 2024 12:06 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/7 "2024-08-13T12:06:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [13 August 2024 12:16 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/8 "2024-08-13T12:16:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [13 August 2024 12:45 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/9 "2024-08-13T12:45:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [13 August 2024 13:39 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/10 "2024-08-13T13:39:29Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [27 August 2024 13:40 UTC](https://discourse.nodered.org/t/calculations-with-values-from-array/90148/11 "2024-08-27T13:40:11Z")

</div>

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