2 inputs from a database to 1 chart, what am I doing wrong?

[questuk]

Hi,

I am getting two individual pieces of data from my database, using a manual Inject node using it's Topic for the Sqlite query.

I can successfully display each individual chart when i click on each Inject node.
But I cannot get them both to display at the same time ?

I have given them different topics, here is an image of my flow and the actual flow.

TEST - V1.04 only DB.json (4.2 KB)

Thanks

[E1cid]

They are two different messages so one overrides the other, you will need to join the messages and feed the combined messages into the chart node.

Not knowing your data structure i can not say more.

[dceejay]

If you also set each flow to have an appropriate msg.topic in one of the change nodes (eg Loft and Utility) they should both render side by side.

[questuk]

Thanks for your replies.
But I have tried for 4 hours to get this to work !

Any chance of showing me a flow, using dummy data instead of my database data, as I think this maybe what is my problem as it seems the chart doesn't know where the data is after all the manipulation ?

I am not familiar with using arrays and using the change node, seems to move that data down another level ?

Thanks

[dceejay]

Ah - apologies - yes - if your are reading two database reads then creating the whole array then indeed - you will be replacing the complete chart...
As usual there a multiple ways to proceed... you can

1. just use two charts
2. change your database query to get all the data in one go and then manipulate it into the required format... or
3. use a join node (probably right after the database nodes and join the results into an object with the two arrays of results - and then do some mad merging to get them sorted and aligned in time... I'm not sure which would be easiest of 2 or 3, neither are simple.

[E1cid]

example

[{"id":"9482236a.e60988","type":"inject","z":"7760f563.ea0324","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"chart1","payload":"[{\"series\":[\"A\",\"B\",\"C\"],\"data\":[[{\"x\":1504029633514,\"y\":6},{\"x\":1504029636622,\"y\":7},{\"x\":1504029639539,\"y\":6}]],\"labels\":[\"\"]}]","payloadType":"json","x":280,"y":1120,"wires":[["243e9b43.83625c"]]},{"id":"4f3f62cb.950fbc","type":"inject","z":"7760f563.ea0324","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"chart2","payload":"[{\"series\":[\"A\",\"B\",\"C\"],\"data\":[[{\"x\":1504029632890,\"y\":5},{\"x\":1504029636001,\"y\":4},{\"x\":1504029638656,\"y\":2}]],\"labels\":[\"\"]}]","payloadType":"json","x":300,"y":1180,"wires":[["243e9b43.83625c"]]},{"id":"243e9b43.83625c","type":"join","z":"7760f563.ea0324","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":"num","reduceFixup":"","x":460,"y":1100,"wires":[["4ebe7ca4.b0a7bc"]]},{"id":"4ebe7ca4.b0a7bc","type":"change","z":"7760f563.ea0324","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"series\":payload.chart1[0].series,\t       \"data\":[\t           [payload.chart1[0].data[0]],\t           [payload.chart2[0].data[0]]\t       ],\t       \"labels\": payload.chart1[0].labels\t   }\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":1100,"wires":[["2eab5727.a113f","3aae1958.34de46"]]},{"id":"3aae1958.34de46","type":"ui_chart","z":"7760f563.ea0324","name":"","group":"165e1e1a.e752fa","order":1,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":850,"y":1100,"wires":[[]]},{"id":"2eab5727.a113f","type":"debug","z":"7760f563.ea0324","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":920,"y":1000,"wires":[]},{"id":"165e1e1a.e752fa","type":"ui_group","z":"","name":"Default","tab":"f9b58883.fa613","order":1,"disp":true,"width":"12","collapse":false},{"id":"f9b58883.fa613","type":"ui_tab","z":"","name":"Home","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

[questuk]

Thanks to you both for your help.

As I have success, I thought I would post my follow up and code.
It took many many hours of searching on the internet and finding small clues as to how to do it.
And I am not a coder, so to anyone else ... this may look obvious of how to do the chart.

And the flow ...

[{"id":"55d1cbb1.b160bc","type":"ui_chart","z":"a13b9737.d91218","name":"","group":"326e8ef0.619b6a","order":1,"width":"15","height":"10","label":"Humidity","chartType":"line","legend":"true","xformat":"dd HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#db2929","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1080,"y":320,"wires":[[],[]]},{"id":"6ae22866.65b858","type":"inject","z":"a13b9737.d91218","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":110,"y":180,"wires":[["2b3468f2.9e8db8"]]},{"id":"2b3468f2.9e8db8","type":"function","z":"a13b9737.d91218","name":"msg.payload = []","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":880,"y":180,"wires":[["55d1cbb1.b160bc","87a53b87.fe7a8"]]},{"id":"f6e59322.620408","type":"sqlite","z":"a13b9737.d91218","mydb":"6fa10898.ea5358","sqlquery":"msg.topic","sql":"","name":"DATA","x":350,"y":280,"wires":[["bb6a7a1c.bd61a8"]]},{"id":"bb6a7a1c.bd61a8","type":"change","z":"a13b9737.d91218","name":"Set data","rules":[{"t":"set","p":"payload","pt":"msg","to":"\t\t  [\t    {\t\t        \"series\": [\"Small Loft\"],\t        \"labels\": [\"H\"],\t        \t       \"data\": [\t                  [\t                    $.payload.\t                      {\t                  \t                        \"x\": $fromMillis(Unix_Time*1000),\t                        \"y\": Value\t                      \t                      }\t                  ]\t                ]\t    }\t  ]\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":280,"wires":[["8d351c4d.e5715"]]},{"id":"3ef59351.8bbad4","type":"sqlite","z":"a13b9737.d91218","mydb":"6fa10898.ea5358","sqlquery":"msg.topic","sql":"","name":"DATA","x":350,"y":360,"wires":[["f1afc0a5.334358"]]},{"id":"f1afc0a5.334358","type":"change","z":"a13b9737.d91218","name":"Set data","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t  {\t\t      \"series\": [\"Utility\"],\t      \"labels\": [\"H\"],\t      \t     \"data\": [\t                [\t                  $.payload.\t                    {\t                \t                      \"x\": $fromMillis(Unix_Time*1000),\t                      \"y\": Value\t                    \t                    }\t                ]\t              ]\t  }\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":360,"wires":[["cdf0d717.0a7de8"]]},{"id":"ed8501de.15fb4","type":"inject","z":"a13b9737.d91218","name":"Utility","topic":"Humidity","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"onceDelay":"","x":90,"y":360,"wires":[["441737ba.e89a9"]]},{"id":"594733a5.b0598c","type":"function","z":"a13b9737.d91218","name":"Query","func":"msg.topic =\n\"SELECT Unix_Time,Value FROM DATA WHERE ID LIKE 'Small Loft - Humidity'\";\nreturn msg;","outputs":1,"noerr":0,"x":230,"y":280,"wires":[["f6e59322.620408"]]},{"id":"798ad7df.75ad1","type":"inject","z":"a13b9737.d91218","name":"Small Loft","topic":"Loft","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"onceDelay":"","x":100,"y":280,"wires":[["594733a5.b0598c"]]},{"id":"441737ba.e89a9","type":"function","z":"a13b9737.d91218","name":"Query","func":"msg.topic =\n\"SELECT Unix_Time,Value FROM DATA WHERE ID LIKE 'Utility - Humidity'\";\nreturn msg;","outputs":1,"noerr":0,"x":230,"y":360,"wires":[["3ef59351.8bbad4"]]},{"id":"8d351c4d.e5715","type":"function","z":"a13b9737.d91218","name":"chart1","func":"msg.topic = \"chart1\";\nreturn msg;","outputs":1,"noerr":0,"x":610,"y":280,"wires":[["2faab590.5f7eea"]]},{"id":"cdf0d717.0a7de8","type":"function","z":"a13b9737.d91218","name":"chart2","func":"msg.topic = \"chart2\";\nreturn msg;","outputs":1,"noerr":0,"x":610,"y":360,"wires":[["2faab590.5f7eea"]]},{"id":"ec261c93.d5aff","type":"debug","z":"a13b9737.d91218","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1070,"y":360,"wires":[]},{"id":"2faab590.5f7eea","type":"join","z":"a13b9737.d91218","name":"Join data","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":760,"y":320,"wires":[["be13cb0a.e413f8"]]},{"id":"be13cb0a.e413f8","type":"change","z":"a13b9737.d91218","name":"Set payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t   \t       \"series\":\t       [\t           [payload.chart1[0].series[0]],\t           [payload.chart2[0].series[0]]   \t       ],\t       \t       \t       \"data\":\t       [\t           [payload.chart1[0].data[0]],\t           [payload.chart2[0].data[0]]\t       ],\t       \t       \t       \"labels\":\t        [\t           [payload.chart1[0].labels[0]],\t           [payload.chart2[0].labels[0]]   \t        ]\t        \t   }\t]\t\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":910,"y":320,"wires":[["55d1cbb1.b160bc","ec261c93.d5aff"]]},{"id":"87a53b87.fe7a8","type":"debug","z":"a13b9737.d91218","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1070,"y":180,"wires":[]},{"id":"326e8ef0.619b6a","type":"ui_group","z":"","name":"Odds","tab":"578e150e.a53944","order":1,"disp":true,"width":"20","collapse":false},{"id":"6fa10898.ea5358","type":"sqlitedb","z":"","db":"/home/gb/Samba_shared_folder/Humidity_V1_00.db","mode":"RW"},{"id":"578e150e.a53944","type":"ui_tab","z":"","name":"Testing","icon":"trending_up","order":6}]