Arrays/Objects/Conversion

smanjunath211 · 11 January 2025 15:08

I am very poor at explaining anything around arrays/objects.
I have a function node before a pdfmake , which is working perfectly.
now the input to my function node has changed due to cleaning up and simplifying my mysql query. but the function is not working anymore, since the data structure has changed.

[{"id":"e726b18ff772c509","type":"change","z":"37ef9ab5d60ffd43","name":"PRODUCTION REPORT (NEW)","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\"HA\":{\"dateshift\":\"10-Jan-A\",\"datehour\":\"HA\",\"sum_m00\":0,\"sum_m01\":1457,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":3212,\"sum_m05\":3515,\"sum_m06\":3110,\"sum_m07\":0,\"sum_m08\":50042,\"sum_m09\":0,\"sum_m10\":240,\"sum_m11\":7227,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":10020,\"sum_m17\":0,\"sum_m18\":19,\"sum_m19\":298,\"sum_m20\":0,\"sum_m21\":2284,\"sum_m22\":1968,\"sum_m23\":1281},\"HB\":{\"dateshift\":\"10-Jan-B\",\"datehour\":\"HB\",\"sum_m00\":0,\"sum_m01\":11165,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1174,\"sum_m05\":2736,\"sum_m06\":2263,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":4608,\"sum_m11\":6178,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":13760,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":365,\"sum_m20\":1970,\"sum_m21\":8820,\"sum_m22\":11065,\"sum_m23\":5008},\"HC\":{\"dateshift\":\"10-Jan-C\",\"datehour\":\"HC\",\"sum_m00\":0,\"sum_m01\":10031,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5309,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":53265,\"sum_m09\":0,\"sum_m10\":11467,\"sum_m11\":5754,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":12243,\"sum_m17\":0,\"sum_m18\":6195,\"sum_m19\":477,\"sum_m20\":2769,\"sum_m21\":41008,\"sum_m22\":40940,\"sum_m23\":8999},\"HD\":{\"dateshift\":\"10-Jan-Day\",\"datehour\":\"HD\",\"sum_m00\":0,\"sum_m01\":22653,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":9695,\"sum_m05\":6251,\"sum_m06\":5373,\"sum_m07\":0,\"sum_m08\":103307,\"sum_m09\":0,\"sum_m10\":16315,\"sum_m11\":19159,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":36023,\"sum_m17\":0,\"sum_m18\":6214,\"sum_m19\":1140,\"sum_m20\":4739,\"sum_m21\":52112,\"sum_m22\":53973,\"sum_m23\":15288}}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":1220,"wires":[["6e7524f2e8f911a2"]]},{"id":"9079d0e13a75e279","type":"inject","z":"37ef9ab5d60ffd43","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":1220,"wires":[["e726b18ff772c509"]]},{"id":"6e7524f2e8f911a2","type":"debug","z":"37ef9ab5d60ffd43","name":"debug 2794","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":620,"y":1220,"wires":[]}]

How do I make these four objects (HA,HB,HC,HD) into an array of objects? i tried function code with javascript, jsonata in a change node, but i am not exactly getting what i need.

E1cid · 11 January 2025 15:21

Set msg. payload
To J: $$.payload.*
If incorrect please show an example of the array of objects you require.

smanjunath211 · 11 January 2025 15:25

Yes, it is pretty much what i need.
Ii slightly modified it so that i can use my existing function code.
please confirm is this correct ? the output looks to me ok.
i will feed it in pdfmake and comeback/

E1cid · 11 January 2025 15:38

So you really required payload as
An object with one property containing an array of objects.

smanjunath211 · 12 January 2025 06:27

so i have a outout from a mysql query with 28 objects. (one aggregate for each hour in a day, 3 aggregate for 3 shifts in a day (8 hour) and one aggregate for the entire day). i need to split them or arrange them into 24+4. the above was an attempt to group, although that was manually done.
I need an algorithm to automatically do this. the structure will always remain same (28 objects), the data may vary.

[{"id":"c770a9bb47876b15","type":"inject","z":"37ef9ab5d60ffd43","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":1420,"wires":[["070a4588aabfa062"]]},{"id":"070a4588aabfa062","type":"change","z":"37ef9ab5d60ffd43","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\"H06\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H06\",\"sum_m00\":0,\"sum_m01\":1,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":260,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":0,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":1,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},\"H07\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H07\",\"sum_m00\":0,\"sum_m01\":1841,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1288,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":2087,\"sum_m09\":0,\"sum_m10\":690,\"sum_m11\":657,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1076,\"sum_m19\":60,\"sum_m20\":499,\"sum_m21\":5399,\"sum_m22\":5404,\"sum_m23\":1148},\"H08\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H08\",\"sum_m00\":0,\"sum_m01\":1741,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":403,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":13356,\"sum_m09\":0,\"sum_m10\":3264,\"sum_m11\":20,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1928,\"sum_m17\":0,\"sum_m18\":1179,\"sum_m19\":43,\"sum_m20\":0,\"sum_m21\":3888,\"sum_m22\":3963,\"sum_m23\":605},\"H09\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H09\",\"sum_m00\":0,\"sum_m01\":1669,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1087,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14268,\"sum_m09\":0,\"sum_m10\":2518,\"sum_m11\":394,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1095,\"sum_m19\":8,\"sum_m20\":541,\"sum_m21\":326,\"sum_m22\":541,\"sum_m23\":0},\"H10\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H10\",\"sum_m00\":0,\"sum_m01\":596,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1022,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14721,\"sum_m09\":0,\"sum_m10\":1849,\"sum_m11\":512,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1656,\"sum_m17\":0,\"sum_m18\":918,\"sum_m19\":49,\"sum_m20\":361,\"sum_m21\":2449,\"sum_m22\":3058,\"sum_m23\":0},\"H11\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H11\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":728,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16047,\"sum_m09\":0,\"sum_m10\":3051,\"sum_m11\":577,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1655,\"sum_m17\":0,\"sum_m18\":866,\"sum_m19\":57,\"sum_m20\":148,\"sum_m21\":1167,\"sum_m22\":4618,\"sum_m23\":0},\"H12\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H12\",\"sum_m00\":0,\"sum_m01\":1251,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":170,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16273,\"sum_m09\":0,\"sum_m10\":2102,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1847,\"sum_m17\":0,\"sum_m18\":932,\"sum_m19\":43,\"sum_m20\":510,\"sum_m21\":3531,\"sum_m22\":4178,\"sum_m23\":0},\"H13\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H13\",\"sum_m00\":0,\"sum_m01\":1656,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":489,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14928,\"sum_m09\":0,\"sum_m10\":238,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2003,\"sum_m17\":0,\"sum_m18\":796,\"sum_m19\":58,\"sum_m20\":0,\"sum_m21\":5279,\"sum_m22\":5452,\"sum_m23\":0},\"H14\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H14\",\"sum_m00\":0,\"sum_m01\":417,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":757,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":50,\"sum_m08\":12552,\"sum_m09\":0,\"sum_m10\":1582,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1780,\"sum_m17\":0,\"sum_m18\":1136,\"sum_m19\":52,\"sum_m20\":177,\"sum_m21\":4204,\"sum_m22\":4247,\"sum_m23\":311},\"H15\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H15\",\"sum_m00\":0,\"sum_m01\":1161,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1250,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":151,\"sum_m08\":11793,\"sum_m09\":0,\"sum_m10\":2732,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1552,\"sum_m17\":0,\"sum_m18\":1023,\"sum_m19\":52,\"sum_m20\":323,\"sum_m21\":4643,\"sum_m22\":4320,\"sum_m23\":1083},\"H16\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H16\",\"sum_m00\":0,\"sum_m01\":365,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":387,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":96,\"sum_m08\":13249,\"sum_m09\":0,\"sum_m10\":811,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2055,\"sum_m17\":0,\"sum_m18\":640,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":3863,\"sum_m22\":5245,\"sum_m23\":157},\"H17\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H17\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":857,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":510,\"sum_m08\":15110,\"sum_m09\":0,\"sum_m10\":2247,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":11,\"sum_m16\":2096,\"sum_m17\":0,\"sum_m18\":890,\"sum_m19\":60,\"sum_m20\":500,\"sum_m21\":4366,\"sum_m22\":5699,\"sum_m23\":697},\"H18\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H18\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":999,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":138,\"sum_m08\":11260,\"sum_m09\":0,\"sum_m10\":658,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":12,\"sum_m16\":1799,\"sum_m17\":0,\"sum_m18\":909,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":1013},\"H19\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H19\",\"sum_m00\":0,\"sum_m01\":1446,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1159,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":509,\"sum_m08\":15852,\"sum_m09\":0,\"sum_m10\":3369,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":243,\"sum_m16\":556,\"sum_m17\":0,\"sum_m18\":1049,\"sum_m19\":60,\"sum_m20\":109,\"sum_m21\":0,\"sum_m22\":3782,\"sum_m23\":810},\"H20\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H20\",\"sum_m00\":0,\"sum_m01\":189,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":113,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":9653,\"sum_m09\":0,\"sum_m10\":104,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":756,\"sum_m17\":0,\"sum_m18\":234,\"sum_m19\":60,\"sum_m20\":260,\"sum_m21\":0,\"sum_m22\":5376,\"sum_m23\":1026},\"H21\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H21\",\"sum_m00\":0,\"sum_m01\":1026,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":399,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":493,\"sum_m08\":12337,\"sum_m09\":0,\"sum_m10\":1913,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":34,\"sum_m16\":1707,\"sum_m17\":0,\"sum_m18\":795,\"sum_m19\":34,\"sum_m20\":0,\"sum_m21\":608,\"sum_m22\":3343,\"sum_m23\":550},\"H22\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H22\",\"sum_m00\":0,\"sum_m01\":1244,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":2,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":631,\"sum_m08\":14288,\"sum_m09\":0,\"sum_m10\":2897,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":20,\"sum_m16\":152,\"sum_m17\":0,\"sum_m18\":844,\"sum_m19\":60,\"sum_m20\":2,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":904},\"H23\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H23\",\"sum_m00\":0,\"sum_m01\":955,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":600,\"sum_m08\":14467,\"sum_m09\":0,\"sum_m10\":2783,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":305,\"sum_m16\":9,\"sum_m17\":0,\"sum_m18\":560,\"sum_m19\":60,\"sum_m20\":568,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":975},\"H00\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H00\",\"sum_m00\":0,\"sum_m01\":101,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":116,\"sum_m08\":13307,\"sum_m09\":0,\"sum_m10\":420,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":35,\"sum_m16\":371,\"sum_m17\":0,\"sum_m18\":574,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":144,\"sum_m22\":4766,\"sum_m23\":1016},\"H01\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H01\",\"sum_m00\":0,\"sum_m01\":33,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":1265,\"sum_m09\":0,\"sum_m10\":1723,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":140,\"sum_m16\":1273,\"sum_m17\":0,\"sum_m18\":1093,\"sum_m19\":27,\"sum_m20\":614,\"sum_m21\":280,\"sum_m22\":2758,\"sum_m23\":1047},\"H02\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H02\",\"sum_m00\":0,\"sum_m01\":14,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":2806,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":110,\"sum_m16\":1459,\"sum_m17\":0,\"sum_m18\":802,\"sum_m19\":48,\"sum_m20\":458,\"sum_m21\":1806,\"sum_m22\":4026,\"sum_m23\":998},\"H03\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H03\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":39,\"sum_m09\":0,\"sum_m10\":48,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":812,\"sum_m17\":0,\"sum_m18\":718,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":5399,\"sum_m22\":5699,\"sum_m23\":688},\"H04\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H04\",\"sum_m00\":0,\"sum_m01\":6,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":7,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":10848,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":327,\"sum_m16\":1407,\"sum_m17\":0,\"sum_m18\":1111,\"sum_m19\":46,\"sum_m20\":0,\"sum_m21\":3974,\"sum_m22\":4293,\"sum_m23\":1043},\"H05\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H05\",\"sum_m00\":0,\"sum_m01\":13,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":92,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14114,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":199,\"sum_m16\":1126,\"sum_m17\":0,\"sum_m18\":1071,\"sum_m19\":29,\"sum_m20\":488,\"sum_m21\":2309,\"sum_m22\":2428,\"sum_m23\":879},\"HA\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"HA\",\"sum_m00\":0,\"sum_m01\":9457,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5954,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":92320,\"sum_m09\":0,\"sum_m10\":13712,\"sum_m11\":2213,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":14367,\"sum_m17\":0,\"sum_m18\":7157,\"sum_m19\":356,\"sum_m20\":2059,\"sum_m21\":22600,\"sum_m22\":29420,\"sum_m23\":2539},\"HB\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"HB\",\"sum_m00\":0,\"sum_m01\":7976,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5921,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1947,\"sum_m08\":101806,\"sum_m09\":0,\"sum_m10\":13416,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":300,\"sum_m16\":12301,\"sum_m17\":0,\"sum_m18\":6676,\"sum_m19\":438,\"sum_m20\":1369,\"sum_m21\":17684,\"sum_m22\":37711,\"sum_m23\":5647},\"HC\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"HC\",\"sum_m00\":0,\"sum_m01\":2376,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":101,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1347,\"sum_m08\":68588,\"sum_m09\":0,\"sum_m10\":10677,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1136,\"sum_m16\":6609,\"sum_m17\":0,\"sum_m18\":6773,\"sum_m19\":391,\"sum_m20\":2130,\"sum_m21\":13912,\"sum_m22\":35368,\"sum_m23\":7550},\"HD\":{\"dateshift\":\"11-Jan-Day\",\"datehour\":\"HD\",\"sum_m00\":0,\"sum_m01\":19809,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":11976,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":3294,\"sum_m08\":262714,\"sum_m09\":0,\"sum_m10\":37805,\"sum_m11\":2214,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1436,\"sum_m16\":33277,\"sum_m17\":0,\"sum_m18\":20606,\"sum_m19\":1185,\"sum_m20\":5558,\"sum_m21\":54196,\"sum_m22\":102499,\"sum_m23\":15736}}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":1420,"wires":[["bb532a0db6bcc8d9"]]},{"id":"bb532a0db6bcc8d9","type":"debug","z":"37ef9ab5d60ffd43","name":"debug 2795","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":480,"y":1420,"wires":[]}]

in the attached sample. i need to regroup
H6,H7,H8,H9,H10,H11,H12,H13,HA --- in querys1
H14,H15,H16,H17,H18,H19,H20,H21,HB -- in querys2
H22,H23,H0,H1,H2,H3,H4,H5,HC-- in querys3
HA,HB,HC,HD -- in querys4

I am still not sure i am conveying the message properly.

jbudd · 12 January 2025 09:03

I am having trouble understanding this.

Maybe it would help if you can show us this SQL query

And an example of the data it produces.

Show us the same data as above in this new format.

How will this data be used once it is formatted?

Perhaps an adjustment to your SQL query will deliver the data in a better format.

E1cid · 12 January 2025 10:03

Can you add the querys1-querys4 in the return data you want to format (maybe as part of the sql query., else you will need a lookup table to covert the data. Which means maintaining a lookup table.

smanjunath211 · 12 January 2025 12:12

How do I get this from mysql query?

How to do that ?

all i know is that if the data to the function node before pdfmake is like this, it works.

i managed to get it to have 28 arrays in one object, but i want only selected 4, in one property or object (i am really messing this up, as i dont know what i am even trying to say)

E1cid · 12 January 2025 12:29

would need more info, like the queries that create the payload, etc

to convert what you have you can as said use a lookup table
e.g.

[{"id":"c770a9bb47876b15","type":"inject","z":"667cec54c048503c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"H06\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H06\",\"sum_m00\":0,\"sum_m01\":1,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":260,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":0,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":1,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},\"H07\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H07\",\"sum_m00\":0,\"sum_m01\":1841,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1288,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":2087,\"sum_m09\":0,\"sum_m10\":690,\"sum_m11\":657,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1076,\"sum_m19\":60,\"sum_m20\":499,\"sum_m21\":5399,\"sum_m22\":5404,\"sum_m23\":1148},\"H08\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H08\",\"sum_m00\":0,\"sum_m01\":1741,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":403,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":13356,\"sum_m09\":0,\"sum_m10\":3264,\"sum_m11\":20,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1928,\"sum_m17\":0,\"sum_m18\":1179,\"sum_m19\":43,\"sum_m20\":0,\"sum_m21\":3888,\"sum_m22\":3963,\"sum_m23\":605},\"H09\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H09\",\"sum_m00\":0,\"sum_m01\":1669,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1087,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14268,\"sum_m09\":0,\"sum_m10\":2518,\"sum_m11\":394,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1095,\"sum_m19\":8,\"sum_m20\":541,\"sum_m21\":326,\"sum_m22\":541,\"sum_m23\":0},\"H10\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H10\",\"sum_m00\":0,\"sum_m01\":596,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1022,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14721,\"sum_m09\":0,\"sum_m10\":1849,\"sum_m11\":512,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1656,\"sum_m17\":0,\"sum_m18\":918,\"sum_m19\":49,\"sum_m20\":361,\"sum_m21\":2449,\"sum_m22\":3058,\"sum_m23\":0},\"H11\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H11\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":728,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16047,\"sum_m09\":0,\"sum_m10\":3051,\"sum_m11\":577,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1655,\"sum_m17\":0,\"sum_m18\":866,\"sum_m19\":57,\"sum_m20\":148,\"sum_m21\":1167,\"sum_m22\":4618,\"sum_m23\":0},\"H12\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H12\",\"sum_m00\":0,\"sum_m01\":1251,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":170,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16273,\"sum_m09\":0,\"sum_m10\":2102,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1847,\"sum_m17\":0,\"sum_m18\":932,\"sum_m19\":43,\"sum_m20\":510,\"sum_m21\":3531,\"sum_m22\":4178,\"sum_m23\":0},\"H13\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H13\",\"sum_m00\":0,\"sum_m01\":1656,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":489,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14928,\"sum_m09\":0,\"sum_m10\":238,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2003,\"sum_m17\":0,\"sum_m18\":796,\"sum_m19\":58,\"sum_m20\":0,\"sum_m21\":5279,\"sum_m22\":5452,\"sum_m23\":0},\"H14\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H14\",\"sum_m00\":0,\"sum_m01\":417,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":757,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":50,\"sum_m08\":12552,\"sum_m09\":0,\"sum_m10\":1582,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1780,\"sum_m17\":0,\"sum_m18\":1136,\"sum_m19\":52,\"sum_m20\":177,\"sum_m21\":4204,\"sum_m22\":4247,\"sum_m23\":311},\"H15\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H15\",\"sum_m00\":0,\"sum_m01\":1161,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1250,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":151,\"sum_m08\":11793,\"sum_m09\":0,\"sum_m10\":2732,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1552,\"sum_m17\":0,\"sum_m18\":1023,\"sum_m19\":52,\"sum_m20\":323,\"sum_m21\":4643,\"sum_m22\":4320,\"sum_m23\":1083},\"H16\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H16\",\"sum_m00\":0,\"sum_m01\":365,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":387,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":96,\"sum_m08\":13249,\"sum_m09\":0,\"sum_m10\":811,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2055,\"sum_m17\":0,\"sum_m18\":640,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":3863,\"sum_m22\":5245,\"sum_m23\":157},\"H17\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H17\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":857,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":510,\"sum_m08\":15110,\"sum_m09\":0,\"sum_m10\":2247,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":11,\"sum_m16\":2096,\"sum_m17\":0,\"sum_m18\":890,\"sum_m19\":60,\"sum_m20\":500,\"sum_m21\":4366,\"sum_m22\":5699,\"sum_m23\":697},\"H18\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H18\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":999,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":138,\"sum_m08\":11260,\"sum_m09\":0,\"sum_m10\":658,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":12,\"sum_m16\":1799,\"sum_m17\":0,\"sum_m18\":909,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":1013},\"H19\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H19\",\"sum_m00\":0,\"sum_m01\":1446,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1159,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":509,\"sum_m08\":15852,\"sum_m09\":0,\"sum_m10\":3369,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":243,\"sum_m16\":556,\"sum_m17\":0,\"sum_m18\":1049,\"sum_m19\":60,\"sum_m20\":109,\"sum_m21\":0,\"sum_m22\":3782,\"sum_m23\":810},\"H20\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H20\",\"sum_m00\":0,\"sum_m01\":189,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":113,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":9653,\"sum_m09\":0,\"sum_m10\":104,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":756,\"sum_m17\":0,\"sum_m18\":234,\"sum_m19\":60,\"sum_m20\":260,\"sum_m21\":0,\"sum_m22\":5376,\"sum_m23\":1026},\"H21\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H21\",\"sum_m00\":0,\"sum_m01\":1026,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":399,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":493,\"sum_m08\":12337,\"sum_m09\":0,\"sum_m10\":1913,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":34,\"sum_m16\":1707,\"sum_m17\":0,\"sum_m18\":795,\"sum_m19\":34,\"sum_m20\":0,\"sum_m21\":608,\"sum_m22\":3343,\"sum_m23\":550},\"H22\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H22\",\"sum_m00\":0,\"sum_m01\":1244,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":2,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":631,\"sum_m08\":14288,\"sum_m09\":0,\"sum_m10\":2897,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":20,\"sum_m16\":152,\"sum_m17\":0,\"sum_m18\":844,\"sum_m19\":60,\"sum_m20\":2,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":904},\"H23\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H23\",\"sum_m00\":0,\"sum_m01\":955,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":600,\"sum_m08\":14467,\"sum_m09\":0,\"sum_m10\":2783,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":305,\"sum_m16\":9,\"sum_m17\":0,\"sum_m18\":560,\"sum_m19\":60,\"sum_m20\":568,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":975},\"H00\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H00\",\"sum_m00\":0,\"sum_m01\":101,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":116,\"sum_m08\":13307,\"sum_m09\":0,\"sum_m10\":420,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":35,\"sum_m16\":371,\"sum_m17\":0,\"sum_m18\":574,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":144,\"sum_m22\":4766,\"sum_m23\":1016},\"H01\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H01\",\"sum_m00\":0,\"sum_m01\":33,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":1265,\"sum_m09\":0,\"sum_m10\":1723,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":140,\"sum_m16\":1273,\"sum_m17\":0,\"sum_m18\":1093,\"sum_m19\":27,\"sum_m20\":614,\"sum_m21\":280,\"sum_m22\":2758,\"sum_m23\":1047},\"H02\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H02\",\"sum_m00\":0,\"sum_m01\":14,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":2806,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":110,\"sum_m16\":1459,\"sum_m17\":0,\"sum_m18\":802,\"sum_m19\":48,\"sum_m20\":458,\"sum_m21\":1806,\"sum_m22\":4026,\"sum_m23\":998},\"H03\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H03\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":39,\"sum_m09\":0,\"sum_m10\":48,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":812,\"sum_m17\":0,\"sum_m18\":718,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":5399,\"sum_m22\":5699,\"sum_m23\":688},\"H04\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H04\",\"sum_m00\":0,\"sum_m01\":6,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":7,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":10848,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":327,\"sum_m16\":1407,\"sum_m17\":0,\"sum_m18\":1111,\"sum_m19\":46,\"sum_m20\":0,\"sum_m21\":3974,\"sum_m22\":4293,\"sum_m23\":1043},\"H05\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H05\",\"sum_m00\":0,\"sum_m01\":13,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":92,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14114,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":199,\"sum_m16\":1126,\"sum_m17\":0,\"sum_m18\":1071,\"sum_m19\":29,\"sum_m20\":488,\"sum_m21\":2309,\"sum_m22\":2428,\"sum_m23\":879},\"HA\":{\"dateshift\":\"11-Jan-A\",\"datehour\":\"HA\",\"sum_m00\":0,\"sum_m01\":9457,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5954,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":92320,\"sum_m09\":0,\"sum_m10\":13712,\"sum_m11\":2213,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":14367,\"sum_m17\":0,\"sum_m18\":7157,\"sum_m19\":356,\"sum_m20\":2059,\"sum_m21\":22600,\"sum_m22\":29420,\"sum_m23\":2539},\"HB\":{\"dateshift\":\"11-Jan-B\",\"datehour\":\"HB\",\"sum_m00\":0,\"sum_m01\":7976,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5921,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1947,\"sum_m08\":101806,\"sum_m09\":0,\"sum_m10\":13416,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":300,\"sum_m16\":12301,\"sum_m17\":0,\"sum_m18\":6676,\"sum_m19\":438,\"sum_m20\":1369,\"sum_m21\":17684,\"sum_m22\":37711,\"sum_m23\":5647},\"HC\":{\"dateshift\":\"11-Jan-C\",\"datehour\":\"HC\",\"sum_m00\":0,\"sum_m01\":2376,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":101,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1347,\"sum_m08\":68588,\"sum_m09\":0,\"sum_m10\":10677,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1136,\"sum_m16\":6609,\"sum_m17\":0,\"sum_m18\":6773,\"sum_m19\":391,\"sum_m20\":2130,\"sum_m21\":13912,\"sum_m22\":35368,\"sum_m23\":7550},\"HD\":{\"dateshift\":\"11-Jan-Day\",\"datehour\":\"HD\",\"sum_m00\":0,\"sum_m01\":19809,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":11976,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":3294,\"sum_m08\":262714,\"sum_m09\":0,\"sum_m10\":37805,\"sum_m11\":2214,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1436,\"sum_m16\":33277,\"sum_m17\":0,\"sum_m18\":20606,\"sum_m19\":1185,\"sum_m20\":5558,\"sum_m21\":54196,\"sum_m22\":102499,\"sum_m23\":15736}}","payloadType":"json","x":430,"y":1640,"wires":[["2c89f67f400d24e2"]]},{"id":"2c89f67f400d24e2","type":"change","z":"667cec54c048503c","name":"","rules":[{"t":"set","p":"lookup","pt":"msg","to":"{\"querys1\":[\"H13\",\"HA\"],\"querys2\":[\"H21\",\"HB\"],\"querys3\":[\"H05\",\"HC\"],\"querys4\":[\"HC\",\"HD\"]}","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"$merge(\t    $each($$.lookup, function($v,$k){\t        {$k:[$v.$lookup($$.payload,$)]}\t    })\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":1700,"wires":[["bb532a0db6bcc8d9"]]},{"id":"bb532a0db6bcc8d9","type":"debug","z":"667cec54c048503c","name":"debug 2795","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":1640,"wires":[]}]

But you have to create the lookup table object if you want to change things.

[edit]
In the queries you can add extra fields
e.g
SELECT colum1, "querys1" AS querys FROM table

This should add an extra property in each array object called querys with the value "querys1". with that field a lookup would not be required.

Someone better a SQL maybe able to show how to return the full object you require straight from the DB query.

smanjunath211 · 12 January 2025 12:29

(SELECT concat(date_format(`rdate`,'%d-%b'),'-',`shift`) as dateshift, 
date_format(`datetime`,'H%H') as datehour, 
{{{payload.0.squery}}}
from rawdata where rdate = '{{rdate}}'
group by rdate, shift, hour(datetime) order by hour(datetime)) UNION ALL
(SELECT concat(date_format(`rdate`,'%d-%b'),'-',`shift`) as dateshift, 
date_format(`datetime`,'HA') as datehour, 
{{{payload.0.squery}}}
from rawdata where rdate = '{{rdate}}' and shift = 'A'
group by shift order by hour(datetime)) UNION ALL
(SELECT concat(date_format(`rdate`,'%d-%b'),'-',`shift`) as dateshift, 
date_format(`datetime`,'HB') as datehour, 
{{{payload.0.squery}}}
from rawdata where rdate = '{{rdate}}' and shift = 'B'
group by shift order by hour(datetime)) UNION ALL
(SELECT concat(date_format(`rdate`,'%d-%b'),'-',`shift`) as dateshift, 
date_format(`datetime`,'HC') as datehour, 
{{{payload.0.squery}}}
from rawdata where rdate = '{{rdate}}' and shift = 'C'
group by shift order by hour(datetime)) UNION ALL
(SELECT concat(date_format(`rdate`,'%d-%b'),'-','Day') as dateshift, 
date_format(`datetime`,'HD') as datehour, 
{{{payload.0.squery}}}
from rawdata where rdate = '{{rdate}}' 
group by rdate order by hour(datetime))

i then split the output from this query and join again with a msg.key for each so that i get H06, H07, H08 etc..

It is in my flow posted earlier. but that is after i split and join.

below is the result of mysql query without doing any processing (before split and join).

[{"dateshift":"11-Jan-A","datehour":"H06","sum_m00":0,"sum_m01":694,"sum_m02":0,"sum_m03":0,"sum_m04":767,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":640,"sum_m09":0,"sum_m10":0,"sum_m11":53,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1114,"sum_m17":0,"sum_m18":295,"sum_m19":38,"sum_m20":0,"sum_m21":561,"sum_m22":2206,"sum_m23":786},{"dateshift":"11-Jan-A","datehour":"H07","sum_m00":0,"sum_m01":1841,"sum_m02":0,"sum_m03":0,"sum_m04":1288,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":2087,"sum_m09":0,"sum_m10":690,"sum_m11":657,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2082,"sum_m17":0,"sum_m18":1076,"sum_m19":60,"sum_m20":499,"sum_m21":5399,"sum_m22":5404,"sum_m23":1148},{"dateshift":"11-Jan-A","datehour":"H08","sum_m00":0,"sum_m01":1741,"sum_m02":0,"sum_m03":0,"sum_m04":403,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":13356,"sum_m09":0,"sum_m10":3264,"sum_m11":20,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1928,"sum_m17":0,"sum_m18":1179,"sum_m19":43,"sum_m20":0,"sum_m21":3888,"sum_m22":3963,"sum_m23":605},{"dateshift":"11-Jan-A","datehour":"H09","sum_m00":0,"sum_m01":1669,"sum_m02":0,"sum_m03":0,"sum_m04":1087,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14268,"sum_m09":0,"sum_m10":2518,"sum_m11":394,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2082,"sum_m17":0,"sum_m18":1095,"sum_m19":8,"sum_m20":541,"sum_m21":326,"sum_m22":541,"sum_m23":0},{"dateshift":"11-Jan-A","datehour":"H10","sum_m00":0,"sum_m01":596,"sum_m02":0,"sum_m03":0,"sum_m04":1022,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14721,"sum_m09":0,"sum_m10":1849,"sum_m11":512,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1656,"sum_m17":0,"sum_m18":918,"sum_m19":49,"sum_m20":361,"sum_m21":2449,"sum_m22":3058,"sum_m23":0},{"dateshift":"11-Jan-A","datehour":"H11","sum_m00":0,"sum_m01":9,"sum_m02":0,"sum_m03":0,"sum_m04":728,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":16047,"sum_m09":0,"sum_m10":3051,"sum_m11":577,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1655,"sum_m17":0,"sum_m18":866,"sum_m19":57,"sum_m20":148,"sum_m21":1167,"sum_m22":4618,"sum_m23":0},{"dateshift":"11-Jan-A","datehour":"H12","sum_m00":0,"sum_m01":1251,"sum_m02":0,"sum_m03":0,"sum_m04":170,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":16273,"sum_m09":0,"sum_m10":2102,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1847,"sum_m17":0,"sum_m18":932,"sum_m19":43,"sum_m20":510,"sum_m21":3531,"sum_m22":4178,"sum_m23":0},{"dateshift":"11-Jan-A","datehour":"H13","sum_m00":0,"sum_m01":1656,"sum_m02":0,"sum_m03":0,"sum_m04":489,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14928,"sum_m09":0,"sum_m10":238,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2003,"sum_m17":0,"sum_m18":796,"sum_m19":58,"sum_m20":0,"sum_m21":5279,"sum_m22":5452,"sum_m23":0},{"dateshift":"11-Jan-B","datehour":"H14","sum_m00":0,"sum_m01":417,"sum_m02":0,"sum_m03":0,"sum_m04":757,"sum_m05":0,"sum_m06":0,"sum_m07":50,"sum_m08":12552,"sum_m09":0,"sum_m10":1582,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1780,"sum_m17":0,"sum_m18":1136,"sum_m19":52,"sum_m20":177,"sum_m21":4204,"sum_m22":4247,"sum_m23":311},{"dateshift":"11-Jan-B","datehour":"H15","sum_m00":0,"sum_m01":1161,"sum_m02":0,"sum_m03":0,"sum_m04":1250,"sum_m05":0,"sum_m06":0,"sum_m07":151,"sum_m08":11793,"sum_m09":0,"sum_m10":2732,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1552,"sum_m17":0,"sum_m18":1023,"sum_m19":52,"sum_m20":323,"sum_m21":4643,"sum_m22":4320,"sum_m23":1083},{"dateshift":"11-Jan-B","datehour":"H16","sum_m00":0,"sum_m01":365,"sum_m02":0,"sum_m03":0,"sum_m04":387,"sum_m05":0,"sum_m06":0,"sum_m07":96,"sum_m08":13249,"sum_m09":0,"sum_m10":811,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2055,"sum_m17":0,"sum_m18":640,"sum_m19":60,"sum_m20":0,"sum_m21":3863,"sum_m22":5245,"sum_m23":157},{"dateshift":"11-Jan-B","datehour":"H17","sum_m00":0,"sum_m01":1686,"sum_m02":0,"sum_m03":0,"sum_m04":857,"sum_m05":0,"sum_m06":0,"sum_m07":510,"sum_m08":15110,"sum_m09":0,"sum_m10":2247,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":11,"sum_m16":2096,"sum_m17":0,"sum_m18":890,"sum_m19":60,"sum_m20":500,"sum_m21":4366,"sum_m22":5699,"sum_m23":697},{"dateshift":"11-Jan-B","datehour":"H18","sum_m00":0,"sum_m01":1686,"sum_m02":0,"sum_m03":0,"sum_m04":999,"sum_m05":0,"sum_m06":0,"sum_m07":138,"sum_m08":11260,"sum_m09":0,"sum_m10":658,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":12,"sum_m16":1799,"sum_m17":0,"sum_m18":909,"sum_m19":60,"sum_m20":0,"sum_m21":0,"sum_m22":5699,"sum_m23":1013},{"dateshift":"11-Jan-B","datehour":"H19","sum_m00":0,"sum_m01":1446,"sum_m02":0,"sum_m03":0,"sum_m04":1159,"sum_m05":0,"sum_m06":0,"sum_m07":509,"sum_m08":15852,"sum_m09":0,"sum_m10":3369,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":243,"sum_m16":556,"sum_m17":0,"sum_m18":1049,"sum_m19":60,"sum_m20":109,"sum_m21":0,"sum_m22":3782,"sum_m23":810},{"dateshift":"11-Jan-B","datehour":"H20","sum_m00":0,"sum_m01":189,"sum_m02":0,"sum_m03":0,"sum_m04":113,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":9653,"sum_m09":0,"sum_m10":104,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":756,"sum_m17":0,"sum_m18":234,"sum_m19":60,"sum_m20":260,"sum_m21":0,"sum_m22":5376,"sum_m23":1026},{"dateshift":"11-Jan-B","datehour":"H21","sum_m00":0,"sum_m01":1026,"sum_m02":0,"sum_m03":0,"sum_m04":399,"sum_m05":0,"sum_m06":0,"sum_m07":493,"sum_m08":12337,"sum_m09":0,"sum_m10":1913,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":34,"sum_m16":1707,"sum_m17":0,"sum_m18":795,"sum_m19":34,"sum_m20":0,"sum_m21":608,"sum_m22":3343,"sum_m23":550},{"dateshift":"11-Jan-C","datehour":"H22","sum_m00":0,"sum_m01":1244,"sum_m02":0,"sum_m03":0,"sum_m04":2,"sum_m05":0,"sum_m06":0,"sum_m07":631,"sum_m08":14288,"sum_m09":0,"sum_m10":2897,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":20,"sum_m16":152,"sum_m17":0,"sum_m18":844,"sum_m19":60,"sum_m20":2,"sum_m21":0,"sum_m22":5699,"sum_m23":904},{"dateshift":"11-Jan-C","datehour":"H23","sum_m00":0,"sum_m01":955,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":600,"sum_m08":14467,"sum_m09":0,"sum_m10":2783,"sum_m11":1,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":305,"sum_m16":9,"sum_m17":0,"sum_m18":560,"sum_m19":60,"sum_m20":568,"sum_m21":0,"sum_m22":5699,"sum_m23":975},{"dateshift":"11-Jan-C","datehour":"H00","sum_m00":0,"sum_m01":101,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":116,"sum_m08":13307,"sum_m09":0,"sum_m10":420,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":35,"sum_m16":371,"sum_m17":0,"sum_m18":574,"sum_m19":60,"sum_m20":0,"sum_m21":144,"sum_m22":4766,"sum_m23":1016},{"dateshift":"11-Jan-C","datehour":"H01","sum_m00":0,"sum_m01":33,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":1265,"sum_m09":0,"sum_m10":1723,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":140,"sum_m16":1273,"sum_m17":0,"sum_m18":1093,"sum_m19":27,"sum_m20":614,"sum_m21":280,"sum_m22":2758,"sum_m23":1047},{"dateshift":"11-Jan-C","datehour":"H02","sum_m00":0,"sum_m01":14,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":0,"sum_m09":0,"sum_m10":2806,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":110,"sum_m16":1459,"sum_m17":0,"sum_m18":802,"sum_m19":48,"sum_m20":458,"sum_m21":1806,"sum_m22":4026,"sum_m23":998},{"dateshift":"11-Jan-C","datehour":"H03","sum_m00":0,"sum_m01":9,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":39,"sum_m09":0,"sum_m10":48,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":812,"sum_m17":0,"sum_m18":718,"sum_m19":60,"sum_m20":0,"sum_m21":5399,"sum_m22":5699,"sum_m23":688},{"dateshift":"11-Jan-C","datehour":"H04","sum_m00":0,"sum_m01":6,"sum_m02":0,"sum_m03":0,"sum_m04":7,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":10848,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":327,"sum_m16":1407,"sum_m17":0,"sum_m18":1111,"sum_m19":46,"sum_m20":0,"sum_m21":3974,"sum_m22":4293,"sum_m23":1043},{"dateshift":"11-Jan-C","datehour":"H05","sum_m00":0,"sum_m01":13,"sum_m02":0,"sum_m03":0,"sum_m04":92,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14114,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":199,"sum_m16":1126,"sum_m17":0,"sum_m18":1071,"sum_m19":29,"sum_m20":488,"sum_m21":2309,"sum_m22":2428,"sum_m23":879},{"dateshift":"11-Jan-C","datehour":"H06","sum_m00":0,"sum_m01":1,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":260,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":0,"sum_m17":0,"sum_m18":0,"sum_m19":1,"sum_m20":0,"sum_m21":0,"sum_m22":0,"sum_m23":0},{"dateshift":"11-Jan-A","datehour":"HA","sum_m00":0,"sum_m01":9457,"sum_m02":0,"sum_m03":0,"sum_m04":5954,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":92320,"sum_m09":0,"sum_m10":13712,"sum_m11":2213,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":14367,"sum_m17":0,"sum_m18":7157,"sum_m19":356,"sum_m20":2059,"sum_m21":22600,"sum_m22":29420,"sum_m23":2539},{"dateshift":"11-Jan-B","datehour":"HB","sum_m00":0,"sum_m01":7976,"sum_m02":0,"sum_m03":0,"sum_m04":5921,"sum_m05":0,"sum_m06":0,"sum_m07":1947,"sum_m08":101806,"sum_m09":0,"sum_m10":13416,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":300,"sum_m16":12301,"sum_m17":0,"sum_m18":6676,"sum_m19":438,"sum_m20":1369,"sum_m21":17684,"sum_m22":37711,"sum_m23":5647},{"dateshift":"11-Jan-C","datehour":"HC","sum_m00":0,"sum_m01":2376,"sum_m02":0,"sum_m03":0,"sum_m04":101,"sum_m05":0,"sum_m06":0,"sum_m07":1347,"sum_m08":68588,"sum_m09":0,"sum_m10":10677,"sum_m11":1,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":1136,"sum_m16":6609,"sum_m17":0,"sum_m18":6773,"sum_m19":391,"sum_m20":2130,"sum_m21":13912,"sum_m22":35368,"sum_m23":7550},{"dateshift":"11-Jan-Day","datehour":"HD","sum_m00":0,"sum_m01":19809,"sum_m02":0,"sum_m03":0,"sum_m04":11976,"sum_m05":0,"sum_m06":0,"sum_m07":3294,"sum_m08":262714,"sum_m09":0,"sum_m10":37805,"sum_m11":2214,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":1436,"sum_m16":33277,"sum_m17":0,"sum_m18":20606,"sum_m19":1185,"sum_m20":5558,"sum_m21":54196,"sum_m22":102499,"sum_m23":15736}]

{"H06":{"dateshift":"11-Jan-C","datehour":"H06","sum_m00":0,"sum_m01":1,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":260,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":0,"sum_m17":0,"sum_m18":0,"sum_m19":1,"sum_m20":0,"sum_m21":0,"sum_m22":0,"sum_m23":0},"H07":{"dateshift":"11-Jan-A","datehour":"H07","sum_m00":0,"sum_m01":1841,"sum_m02":0,"sum_m03":0,"sum_m04":1288,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":2087,"sum_m09":0,"sum_m10":690,"sum_m11":657,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2082,"sum_m17":0,"sum_m18":1076,"sum_m19":60,"sum_m20":499,"sum_m21":5399,"sum_m22":5404,"sum_m23":1148},"H08":{"dateshift":"11-Jan-A","datehour":"H08","sum_m00":0,"sum_m01":1741,"sum_m02":0,"sum_m03":0,"sum_m04":403,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":13356,"sum_m09":0,"sum_m10":3264,"sum_m11":20,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1928,"sum_m17":0,"sum_m18":1179,"sum_m19":43,"sum_m20":0,"sum_m21":3888,"sum_m22":3963,"sum_m23":605},"H09":{"dateshift":"11-Jan-A","datehour":"H09","sum_m00":0,"sum_m01":1669,"sum_m02":0,"sum_m03":0,"sum_m04":1087,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14268,"sum_m09":0,"sum_m10":2518,"sum_m11":394,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2082,"sum_m17":0,"sum_m18":1095,"sum_m19":8,"sum_m20":541,"sum_m21":326,"sum_m22":541,"sum_m23":0},"H10":{"dateshift":"11-Jan-A","datehour":"H10","sum_m00":0,"sum_m01":596,"sum_m02":0,"sum_m03":0,"sum_m04":1022,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14721,"sum_m09":0,"sum_m10":1849,"sum_m11":512,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1656,"sum_m17":0,"sum_m18":918,"sum_m19":49,"sum_m20":361,"sum_m21":2449,"sum_m22":3058,"sum_m23":0},"H11":{"dateshift":"11-Jan-A","datehour":"H11","sum_m00":0,"sum_m01":9,"sum_m02":0,"sum_m03":0,"sum_m04":728,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":16047,"sum_m09":0,"sum_m10":3051,"sum_m11":577,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1655,"sum_m17":0,"sum_m18":866,"sum_m19":57,"sum_m20":148,"sum_m21":1167,"sum_m22":4618,"sum_m23":0},"H12":{"dateshift":"11-Jan-A","datehour":"H12","sum_m00":0,"sum_m01":1251,"sum_m02":0,"sum_m03":0,"sum_m04":170,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":16273,"sum_m09":0,"sum_m10":2102,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1847,"sum_m17":0,"sum_m18":932,"sum_m19":43,"sum_m20":510,"sum_m21":3531,"sum_m22":4178,"sum_m23":0},"H13":{"dateshift":"11-Jan-A","datehour":"H13","sum_m00":0,"sum_m01":1656,"sum_m02":0,"sum_m03":0,"sum_m04":489,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14928,"sum_m09":0,"sum_m10":238,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2003,"sum_m17":0,"sum_m18":796,"sum_m19":58,"sum_m20":0,"sum_m21":5279,"sum_m22":5452,"sum_m23":0},"H14":{"dateshift":"11-Jan-B","datehour":"H14","sum_m00":0,"sum_m01":417,"sum_m02":0,"sum_m03":0,"sum_m04":757,"sum_m05":0,"sum_m06":0,"sum_m07":50,"sum_m08":12552,"sum_m09":0,"sum_m10":1582,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1780,"sum_m17":0,"sum_m18":1136,"sum_m19":52,"sum_m20":177,"sum_m21":4204,"sum_m22":4247,"sum_m23":311},"H15":{"dateshift":"11-Jan-B","datehour":"H15","sum_m00":0,"sum_m01":1161,"sum_m02":0,"sum_m03":0,"sum_m04":1250,"sum_m05":0,"sum_m06":0,"sum_m07":151,"sum_m08":11793,"sum_m09":0,"sum_m10":2732,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":1552,"sum_m17":0,"sum_m18":1023,"sum_m19":52,"sum_m20":323,"sum_m21":4643,"sum_m22":4320,"sum_m23":1083},"H16":{"dateshift":"11-Jan-B","datehour":"H16","sum_m00":0,"sum_m01":365,"sum_m02":0,"sum_m03":0,"sum_m04":387,"sum_m05":0,"sum_m06":0,"sum_m07":96,"sum_m08":13249,"sum_m09":0,"sum_m10":811,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":2055,"sum_m17":0,"sum_m18":640,"sum_m19":60,"sum_m20":0,"sum_m21":3863,"sum_m22":5245,"sum_m23":157},"H17":{"dateshift":"11-Jan-B","datehour":"H17","sum_m00":0,"sum_m01":1686,"sum_m02":0,"sum_m03":0,"sum_m04":857,"sum_m05":0,"sum_m06":0,"sum_m07":510,"sum_m08":15110,"sum_m09":0,"sum_m10":2247,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":11,"sum_m16":2096,"sum_m17":0,"sum_m18":890,"sum_m19":60,"sum_m20":500,"sum_m21":4366,"sum_m22":5699,"sum_m23":697},"H18":{"dateshift":"11-Jan-B","datehour":"H18","sum_m00":0,"sum_m01":1686,"sum_m02":0,"sum_m03":0,"sum_m04":999,"sum_m05":0,"sum_m06":0,"sum_m07":138,"sum_m08":11260,"sum_m09":0,"sum_m10":658,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":12,"sum_m16":1799,"sum_m17":0,"sum_m18":909,"sum_m19":60,"sum_m20":0,"sum_m21":0,"sum_m22":5699,"sum_m23":1013},"H19":{"dateshift":"11-Jan-B","datehour":"H19","sum_m00":0,"sum_m01":1446,"sum_m02":0,"sum_m03":0,"sum_m04":1159,"sum_m05":0,"sum_m06":0,"sum_m07":509,"sum_m08":15852,"sum_m09":0,"sum_m10":3369,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":243,"sum_m16":556,"sum_m17":0,"sum_m18":1049,"sum_m19":60,"sum_m20":109,"sum_m21":0,"sum_m22":3782,"sum_m23":810},"H20":{"dateshift":"11-Jan-B","datehour":"H20","sum_m00":0,"sum_m01":189,"sum_m02":0,"sum_m03":0,"sum_m04":113,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":9653,"sum_m09":0,"sum_m10":104,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":756,"sum_m17":0,"sum_m18":234,"sum_m19":60,"sum_m20":260,"sum_m21":0,"sum_m22":5376,"sum_m23":1026},"H21":{"dateshift":"11-Jan-B","datehour":"H21","sum_m00":0,"sum_m01":1026,"sum_m02":0,"sum_m03":0,"sum_m04":399,"sum_m05":0,"sum_m06":0,"sum_m07":493,"sum_m08":12337,"sum_m09":0,"sum_m10":1913,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":34,"sum_m16":1707,"sum_m17":0,"sum_m18":795,"sum_m19":34,"sum_m20":0,"sum_m21":608,"sum_m22":3343,"sum_m23":550},"H22":{"dateshift":"11-Jan-C","datehour":"H22","sum_m00":0,"sum_m01":1244,"sum_m02":0,"sum_m03":0,"sum_m04":2,"sum_m05":0,"sum_m06":0,"sum_m07":631,"sum_m08":14288,"sum_m09":0,"sum_m10":2897,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":20,"sum_m16":152,"sum_m17":0,"sum_m18":844,"sum_m19":60,"sum_m20":2,"sum_m21":0,"sum_m22":5699,"sum_m23":904},"H23":{"dateshift":"11-Jan-C","datehour":"H23","sum_m00":0,"sum_m01":955,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":600,"sum_m08":14467,"sum_m09":0,"sum_m10":2783,"sum_m11":1,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":305,"sum_m16":9,"sum_m17":0,"sum_m18":560,"sum_m19":60,"sum_m20":568,"sum_m21":0,"sum_m22":5699,"sum_m23":975},"H00":{"dateshift":"11-Jan-C","datehour":"H00","sum_m00":0,"sum_m01":101,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":116,"sum_m08":13307,"sum_m09":0,"sum_m10":420,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":35,"sum_m16":371,"sum_m17":0,"sum_m18":574,"sum_m19":60,"sum_m20":0,"sum_m21":144,"sum_m22":4766,"sum_m23":1016},"H01":{"dateshift":"11-Jan-C","datehour":"H01","sum_m00":0,"sum_m01":33,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":1265,"sum_m09":0,"sum_m10":1723,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":140,"sum_m16":1273,"sum_m17":0,"sum_m18":1093,"sum_m19":27,"sum_m20":614,"sum_m21":280,"sum_m22":2758,"sum_m23":1047},"H02":{"dateshift":"11-Jan-C","datehour":"H02","sum_m00":0,"sum_m01":14,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":0,"sum_m09":0,"sum_m10":2806,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":110,"sum_m16":1459,"sum_m17":0,"sum_m18":802,"sum_m19":48,"sum_m20":458,"sum_m21":1806,"sum_m22":4026,"sum_m23":998},"H03":{"dateshift":"11-Jan-C","datehour":"H03","sum_m00":0,"sum_m01":9,"sum_m02":0,"sum_m03":0,"sum_m04":0,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":39,"sum_m09":0,"sum_m10":48,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":812,"sum_m17":0,"sum_m18":718,"sum_m19":60,"sum_m20":0,"sum_m21":5399,"sum_m22":5699,"sum_m23":688},"H04":{"dateshift":"11-Jan-C","datehour":"H04","sum_m00":0,"sum_m01":6,"sum_m02":0,"sum_m03":0,"sum_m04":7,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":10848,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":327,"sum_m16":1407,"sum_m17":0,"sum_m18":1111,"sum_m19":46,"sum_m20":0,"sum_m21":3974,"sum_m22":4293,"sum_m23":1043},"H05":{"dateshift":"11-Jan-C","datehour":"H05","sum_m00":0,"sum_m01":13,"sum_m02":0,"sum_m03":0,"sum_m04":92,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":14114,"sum_m09":0,"sum_m10":0,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":199,"sum_m16":1126,"sum_m17":0,"sum_m18":1071,"sum_m19":29,"sum_m20":488,"sum_m21":2309,"sum_m22":2428,"sum_m23":879},"HA":{"dateshift":"11-Jan-A","datehour":"HA","sum_m00":0,"sum_m01":9457,"sum_m02":0,"sum_m03":0,"sum_m04":5954,"sum_m05":0,"sum_m06":0,"sum_m07":0,"sum_m08":92320,"sum_m09":0,"sum_m10":13712,"sum_m11":2213,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":0,"sum_m16":14367,"sum_m17":0,"sum_m18":7157,"sum_m19":356,"sum_m20":2059,"sum_m21":22600,"sum_m22":29420,"sum_m23":2539},"HB":{"dateshift":"11-Jan-B","datehour":"HB","sum_m00":0,"sum_m01":7976,"sum_m02":0,"sum_m03":0,"sum_m04":5921,"sum_m05":0,"sum_m06":0,"sum_m07":1947,"sum_m08":101806,"sum_m09":0,"sum_m10":13416,"sum_m11":0,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":300,"sum_m16":12301,"sum_m17":0,"sum_m18":6676,"sum_m19":438,"sum_m20":1369,"sum_m21":17684,"sum_m22":37711,"sum_m23":5647},"HC":{"dateshift":"11-Jan-C","datehour":"HC","sum_m00":0,"sum_m01":2376,"sum_m02":0,"sum_m03":0,"sum_m04":101,"sum_m05":0,"sum_m06":0,"sum_m07":1347,"sum_m08":68588,"sum_m09":0,"sum_m10":10677,"sum_m11":1,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":1136,"sum_m16":6609,"sum_m17":0,"sum_m18":6773,"sum_m19":391,"sum_m20":2130,"sum_m21":13912,"sum_m22":35368,"sum_m23":7550},"HD":{"dateshift":"11-Jan-Day","datehour":"HD","sum_m00":0,"sum_m01":19809,"sum_m02":0,"sum_m03":0,"sum_m04":11976,"sum_m05":0,"sum_m06":0,"sum_m07":3294,"sum_m08":262714,"sum_m09":0,"sum_m10":37805,"sum_m11":2214,"sum_m12":0,"sum_m13":0,"sum_m14":0,"sum_m15":1436,"sum_m16":33277,"sum_m17":0,"sum_m18":20606,"sum_m19":1185,"sum_m20":5558,"sum_m21":54196,"sum_m22":102499,"sum_m23":15736}}

it is used in a function node just before a pdfmake node to create a pdf.

the function node (i still dont have it for the NEW data, but this is for old data.

[{"id":"cdfadeb88e48475a","type":"function","z":"37ef9ab5d60ffd43","name":"data to pdf","func":"///\nlet querys1Data = []\nmsg.payload.querys1.forEach(obj => { querys1Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true, alignment: 'left' } })) })\nlet querys2Data = []\nmsg.payload.querys2.forEach(obj => { querys2Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\nlet querys3Data = []\nmsg.payload.querys3.forEach(obj => { querys3Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\nlet querys4Data = []\nmsg.payload.querys4.forEach(obj => { querys4Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\n\n///\n\nlet tableData = {\n\n    // pageSize: 'a3',\n    pageSize: {    width: 1100,    height: 'auto'    },    pageOrientation: 'portrait',\n\n    content: [\n        { text: flow.get(\"pdf_header1\"), fontSize: 20, bold: true, alignment: 'center', color: 'green', margin: [12, 0, 12, 0] },\n        { text: ' ', fontSize: 20, bold: true, margin: [0, 0, 0, 0] },\n\n        { text: flow.get(\"production_header\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },        \n    {\n                        style: 'tableExample',\n            table: {\n                headerRows: 1,\n                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys1[0]).map(key => { return { text: key, alignment: key.startsWith(\"Shift\") ? \"left\" : \"right\", style: 'tableHeader' } }),\n                    ...querys1Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n        {text: '---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------', \nfontSize: 10, bold: false, color: 'orange', margin: [0, 0, 0, 5] },\n\n        { text: flow.get(\"shifta\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys2[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\", style: 'tableHeader' } }),\n                    ...querys2Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        { text: flow.get(\"shiftb\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys3[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\",style: 'tableHeader' } }),\n                    ...querys3Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        { text: flow.get(\"shiftc\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys4[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\",style: 'tableHeader' } }),\n                    ...querys4Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        \n        \n    ],\n    \n\n    styles: {\n        header: {\n            fontSize: 12,\n            bold: true,\n            margin: [12, 2, 12, 2]\n        },\n        subheader: {\n            fontSize: 12,\n            bold: true,\n            margin: [12, 2, 12, 2]\n        },\n        tableExample: {\n            fontSize: 10,\n            bold: false,\n        },\n        tableHeader: {\n            bold: true,\n            fontSize: 12,\n            color: 'red'\n        }\n    },\n    defaultStyle: {\n        // alignment: 'justify'\n        }\n\n};\n\nmsg.payload = tableData;\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":720,"wires":[["4388cf246c5b15f5"]]}]

smanjunath211 · 12 January 2025 12:48

this looks good to me.

I will work on this...

thank you.

E1cid · 12 January 2025 13:05

You can skip the split join section by using this expression.

[{"id":"6a6110105955e2cd","type":"inject","z":"667cec54c048503c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H06\",\"sum_m00\":0,\"sum_m01\":694,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":767,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":640,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":53,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1114,\"sum_m17\":0,\"sum_m18\":295,\"sum_m19\":38,\"sum_m20\":0,\"sum_m21\":561,\"sum_m22\":2206,\"sum_m23\":786},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H07\",\"sum_m00\":0,\"sum_m01\":1841,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1288,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":2087,\"sum_m09\":0,\"sum_m10\":690,\"sum_m11\":657,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1076,\"sum_m19\":60,\"sum_m20\":499,\"sum_m21\":5399,\"sum_m22\":5404,\"sum_m23\":1148},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H08\",\"sum_m00\":0,\"sum_m01\":1741,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":403,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":13356,\"sum_m09\":0,\"sum_m10\":3264,\"sum_m11\":20,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1928,\"sum_m17\":0,\"sum_m18\":1179,\"sum_m19\":43,\"sum_m20\":0,\"sum_m21\":3888,\"sum_m22\":3963,\"sum_m23\":605},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H09\",\"sum_m00\":0,\"sum_m01\":1669,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1087,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14268,\"sum_m09\":0,\"sum_m10\":2518,\"sum_m11\":394,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2082,\"sum_m17\":0,\"sum_m18\":1095,\"sum_m19\":8,\"sum_m20\":541,\"sum_m21\":326,\"sum_m22\":541,\"sum_m23\":0},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H10\",\"sum_m00\":0,\"sum_m01\":596,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1022,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14721,\"sum_m09\":0,\"sum_m10\":1849,\"sum_m11\":512,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1656,\"sum_m17\":0,\"sum_m18\":918,\"sum_m19\":49,\"sum_m20\":361,\"sum_m21\":2449,\"sum_m22\":3058,\"sum_m23\":0},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H11\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":728,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16047,\"sum_m09\":0,\"sum_m10\":3051,\"sum_m11\":577,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1655,\"sum_m17\":0,\"sum_m18\":866,\"sum_m19\":57,\"sum_m20\":148,\"sum_m21\":1167,\"sum_m22\":4618,\"sum_m23\":0},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H12\",\"sum_m00\":0,\"sum_m01\":1251,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":170,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":16273,\"sum_m09\":0,\"sum_m10\":2102,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1847,\"sum_m17\":0,\"sum_m18\":932,\"sum_m19\":43,\"sum_m20\":510,\"sum_m21\":3531,\"sum_m22\":4178,\"sum_m23\":0},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"H13\",\"sum_m00\":0,\"sum_m01\":1656,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":489,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14928,\"sum_m09\":0,\"sum_m10\":238,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2003,\"sum_m17\":0,\"sum_m18\":796,\"sum_m19\":58,\"sum_m20\":0,\"sum_m21\":5279,\"sum_m22\":5452,\"sum_m23\":0},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H14\",\"sum_m00\":0,\"sum_m01\":417,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":757,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":50,\"sum_m08\":12552,\"sum_m09\":0,\"sum_m10\":1582,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1780,\"sum_m17\":0,\"sum_m18\":1136,\"sum_m19\":52,\"sum_m20\":177,\"sum_m21\":4204,\"sum_m22\":4247,\"sum_m23\":311},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H15\",\"sum_m00\":0,\"sum_m01\":1161,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1250,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":151,\"sum_m08\":11793,\"sum_m09\":0,\"sum_m10\":2732,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1552,\"sum_m17\":0,\"sum_m18\":1023,\"sum_m19\":52,\"sum_m20\":323,\"sum_m21\":4643,\"sum_m22\":4320,\"sum_m23\":1083},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H16\",\"sum_m00\":0,\"sum_m01\":365,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":387,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":96,\"sum_m08\":13249,\"sum_m09\":0,\"sum_m10\":811,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2055,\"sum_m17\":0,\"sum_m18\":640,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":3863,\"sum_m22\":5245,\"sum_m23\":157},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H17\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":857,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":510,\"sum_m08\":15110,\"sum_m09\":0,\"sum_m10\":2247,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":11,\"sum_m16\":2096,\"sum_m17\":0,\"sum_m18\":890,\"sum_m19\":60,\"sum_m20\":500,\"sum_m21\":4366,\"sum_m22\":5699,\"sum_m23\":697},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H18\",\"sum_m00\":0,\"sum_m01\":1686,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":999,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":138,\"sum_m08\":11260,\"sum_m09\":0,\"sum_m10\":658,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":12,\"sum_m16\":1799,\"sum_m17\":0,\"sum_m18\":909,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":1013},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H19\",\"sum_m00\":0,\"sum_m01\":1446,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1159,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":509,\"sum_m08\":15852,\"sum_m09\":0,\"sum_m10\":3369,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":243,\"sum_m16\":556,\"sum_m17\":0,\"sum_m18\":1049,\"sum_m19\":60,\"sum_m20\":109,\"sum_m21\":0,\"sum_m22\":3782,\"sum_m23\":810},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H20\",\"sum_m00\":0,\"sum_m01\":189,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":113,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":9653,\"sum_m09\":0,\"sum_m10\":104,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":756,\"sum_m17\":0,\"sum_m18\":234,\"sum_m19\":60,\"sum_m20\":260,\"sum_m21\":0,\"sum_m22\":5376,\"sum_m23\":1026},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"H21\",\"sum_m00\":0,\"sum_m01\":1026,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":399,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":493,\"sum_m08\":12337,\"sum_m09\":0,\"sum_m10\":1913,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":34,\"sum_m16\":1707,\"sum_m17\":0,\"sum_m18\":795,\"sum_m19\":34,\"sum_m20\":0,\"sum_m21\":608,\"sum_m22\":3343,\"sum_m23\":550},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H22\",\"sum_m00\":0,\"sum_m01\":1244,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":2,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":631,\"sum_m08\":14288,\"sum_m09\":0,\"sum_m10\":2897,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":20,\"sum_m16\":152,\"sum_m17\":0,\"sum_m18\":844,\"sum_m19\":60,\"sum_m20\":2,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":904},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H23\",\"sum_m00\":0,\"sum_m01\":955,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":600,\"sum_m08\":14467,\"sum_m09\":0,\"sum_m10\":2783,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":305,\"sum_m16\":9,\"sum_m17\":0,\"sum_m18\":560,\"sum_m19\":60,\"sum_m20\":568,\"sum_m21\":0,\"sum_m22\":5699,\"sum_m23\":975},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H00\",\"sum_m00\":0,\"sum_m01\":101,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":116,\"sum_m08\":13307,\"sum_m09\":0,\"sum_m10\":420,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":35,\"sum_m16\":371,\"sum_m17\":0,\"sum_m18\":574,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":144,\"sum_m22\":4766,\"sum_m23\":1016},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H01\",\"sum_m00\":0,\"sum_m01\":33,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":1265,\"sum_m09\":0,\"sum_m10\":1723,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":140,\"sum_m16\":1273,\"sum_m17\":0,\"sum_m18\":1093,\"sum_m19\":27,\"sum_m20\":614,\"sum_m21\":280,\"sum_m22\":2758,\"sum_m23\":1047},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H02\",\"sum_m00\":0,\"sum_m01\":14,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":2806,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":110,\"sum_m16\":1459,\"sum_m17\":0,\"sum_m18\":802,\"sum_m19\":48,\"sum_m20\":458,\"sum_m21\":1806,\"sum_m22\":4026,\"sum_m23\":998},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H03\",\"sum_m00\":0,\"sum_m01\":9,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":39,\"sum_m09\":0,\"sum_m10\":48,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":812,\"sum_m17\":0,\"sum_m18\":718,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":5399,\"sum_m22\":5699,\"sum_m23\":688},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H04\",\"sum_m00\":0,\"sum_m01\":6,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":7,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":10848,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":327,\"sum_m16\":1407,\"sum_m17\":0,\"sum_m18\":1111,\"sum_m19\":46,\"sum_m20\":0,\"sum_m21\":3974,\"sum_m22\":4293,\"sum_m23\":1043},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H05\",\"sum_m00\":0,\"sum_m01\":13,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":92,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":14114,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":199,\"sum_m16\":1126,\"sum_m17\":0,\"sum_m18\":1071,\"sum_m19\":29,\"sum_m20\":488,\"sum_m21\":2309,\"sum_m22\":2428,\"sum_m23\":879},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"H06\",\"sum_m00\":0,\"sum_m01\":1,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":260,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":0,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":1,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},{\"dateshift\":\"11-Jan-A\",\"datehour\":\"HA\",\"sum_m00\":0,\"sum_m01\":9457,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5954,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":92320,\"sum_m09\":0,\"sum_m10\":13712,\"sum_m11\":2213,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":14367,\"sum_m17\":0,\"sum_m18\":7157,\"sum_m19\":356,\"sum_m20\":2059,\"sum_m21\":22600,\"sum_m22\":29420,\"sum_m23\":2539},{\"dateshift\":\"11-Jan-B\",\"datehour\":\"HB\",\"sum_m00\":0,\"sum_m01\":7976,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5921,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1947,\"sum_m08\":101806,\"sum_m09\":0,\"sum_m10\":13416,\"sum_m11\":0,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":300,\"sum_m16\":12301,\"sum_m17\":0,\"sum_m18\":6676,\"sum_m19\":438,\"sum_m20\":1369,\"sum_m21\":17684,\"sum_m22\":37711,\"sum_m23\":5647},{\"dateshift\":\"11-Jan-C\",\"datehour\":\"HC\",\"sum_m00\":0,\"sum_m01\":2376,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":101,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":1347,\"sum_m08\":68588,\"sum_m09\":0,\"sum_m10\":10677,\"sum_m11\":1,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1136,\"sum_m16\":6609,\"sum_m17\":0,\"sum_m18\":6773,\"sum_m19\":391,\"sum_m20\":2130,\"sum_m21\":13912,\"sum_m22\":35368,\"sum_m23\":7550},{\"dateshift\":\"11-Jan-Day\",\"datehour\":\"HD\",\"sum_m00\":0,\"sum_m01\":19809,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":11976,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":3294,\"sum_m08\":262714,\"sum_m09\":0,\"sum_m10\":37805,\"sum_m11\":2214,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":1436,\"sum_m16\":33277,\"sum_m17\":0,\"sum_m18\":20606,\"sum_m19\":1185,\"sum_m20\":5558,\"sum_m21\":54196,\"sum_m22\":102499,\"sum_m23\":15736}]","payloadType":"json","x":430,"y":1560,"wires":[["2c89f67f400d24e2"]]},{"id":"2c89f67f400d24e2","type":"change","z":"667cec54c048503c","name":"","rules":[{"t":"set","p":"lookup","pt":"msg","to":"{\"querys1\":[\"H13\",\"HA\"],\"querys2\":[\"H21\",\"HB\"],\"querys3\":[\"H05\",\"HC\"],\"querys4\":[\"HC\",\"HD\"]}","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"$merge(\t    $each($$.lookup, function($v,$k){\t        {\t            $k:[$v.$single($$.payload, function($vo,$io){\t            $vo.datehour = $\t            })]\t        }\t    })\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":1700,"wires":[["bb532a0db6bcc8d9"]]},{"id":"bb532a0db6bcc8d9","type":"debug","z":"667cec54c048503c","name":"debug 2795","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":1640,"wires":[]}]

On the data prior to the split join.

jbudd · 12 January 2025 13:52

Can you also share the actual field names and aggregate functions in the query - ie a typical value for {{{payload.0.squery}}}?

smanjunath211 · 12 January 2025 13:59

that is a very long query, so i first generate a query using a select statement and then execute that in another query.

the below is the mysql query that generates the {{payload.0.squery}}

SELECT
    (
        SELECT 
            GROUP_CONCAT(
                CONCAT(' SUM(COALESCE(`', column_name, '`, 0)) AS `sum_', column_name,'`')
            )
        FROM
            information_schema.columns
        WHERE
            table_schema = 'dfpl'
            AND table_name = 'rawdata'
            AND column_name REGEXP '^m[0-9]+$'
     ) AS squery
FROM
    rawdata
limit 1

Here is one data for {{payload.0.squery}}

 SUM(COALESCE(`m00`, 0)) AS `sum_m00`, SUM(COALESCE(`m01`, 0)) AS `sum_m01`, SUM(COALESCE(`m02`, 0)) AS `sum_m02`, SUM(COALESCE(`m03`, 0)) AS `sum_m03`, SUM(COALESCE(`m04`, 0)) AS `sum_m04`, SUM(COALESCE(`m05`, 0)) AS `sum_m05`, SUM(COALESCE(`m06`, 0)) AS `sum_m06`, SUM(COALESCE(`m07`, 0)) AS `sum_m07`, SUM(COALESCE(`m08`, 0)) AS `sum_m08`, SUM(COALESCE(`m09`, 0)) AS `sum_m09`, SUM(COALESCE(`m10`, 0)) AS `sum_m10`, SUM(COALESCE(`m11`, 0)) AS `sum_m11`, SUM(COALESCE(`m12`, 0)) AS `sum_m12`, SUM(COALESCE(`m13`, 0)) AS `sum_m13`, SUM(COALESCE(`m14`, 0)) AS `sum_m14`, SUM(COALESCE(`m15`, 0)) AS `sum_m15`, SUM(COALESCE(`m16`, 0)) AS `sum_m16`, SUM(COALESCE(`m17`, 0)) AS `sum_m17`, SUM(COALESCE(`m18`, 0)) AS `sum_m18`, SUM(COALESCE(`m19`, 0)) AS `sum_m19`, SUM(COALESCE(`m20`, 0)) AS `sum_m20`, SUM(COALESCE(`m21`, 0)) AS `sum_m21`, SUM(COALESCE(`m22`, 0)) AS `sum_m22`, SUM(COALESCE(`m23`, 0)) AS `sum_m23`

my database is actually having a datetime field, and around 20 machines columns, and the frequency of logging is 1 minute. the mysql query is trying to give back the hourly total of each machine, 8 hourly total and a daily total.

smanjunath211 · 12 January 2025 14:02

MAGIC!!
looks like I am almost there.

@jbudd is trying to help as well in mysql query. combined, i will get what i want i hope.

Off to connect to pdfmake function now.

smanjunath211 · 12 January 2025 14:26

Changing the msg.lookup made this stop working. how do i change the lookup table, without breaking this.

"Invalid JSONata expression: The $single() function expected exactly 1 matching result. Instead it matched more."

my msg.lookup is like this

{"querys1":["HA","HB","HC","HD"],
"querys2":["H06","H07","H08","H09","H10","H11","H12","H13","HA"],
"querys3":["H14","H15","H16","H17","H18","H19","H20","H21","HB"],
"querys4":["H22","H23","H00","H01","H02","H03","H04","H05","HC"]}

E1cid · 12 January 2025 16:04

You have 2 objects with H06 as datehour in the payload object, which one do you want to keep?

this will keep the first

$merge(
    $each($$.lookup, function($v,$k){
        {
            $k:[$v.$filter($$.payload, function($vo){
            $vo.datehour = $
            })[0]]
        }
    })
)

[-1] will add last object
removing [0] will add both objects

smanjunath211 · 12 January 2025 16:27

Aha. there is one minute of data crossing over from previous day, which shouldn't. i will rectify the mysql database update statement.

appreciate your input to even giving solution to this unintended scenario!

smanjunath211 · 13 January 2025 01:26

Done! successfuly generated pdf with the new flow

[{"id":"ec11c4c479035a8b","type":"group","z":"37ef9ab5d60ffd43","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["e7acbd52452fd432","b682b9984edb4eeb","1f3a0c4b25900303","3b65129d5fc39fba","19f1d07d833c4089","b2d12f4267a996af","cbb706cb5d296bde","351f4a478470d01a","5a0e6d1cd6577bbf","a14e0509d666af81","1aace55de5fe6514","82f86fcf50667ffb","c2841fd1eca9125a","b7d5e5bb0c82fbd7"],"x":154,"y":2599,"w":1302,"h":322},{"id":"e7acbd52452fd432","type":"change","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"PRODUCTION REPORT (OLD)","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\"querys1\":[{\"Shift\":\"08-A\",\"CR3\":1970,\"MSP1\":0,\"BTP1\":0,\"SP4\":2116,\"SP3\":4579,\"PFSL\":1736,\"PFSR\":1643,\"CR1\":1490,\"JUC\":77226,\"CFT\":0,\"14T\":0,\"12T\":3,\"BTP3\":42051,\"DIP\":850,\"BTP2\":41515,\"SP5\":3727,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Shift\":\"08-B\",\"CR3\":1879,\"MSP1\":0,\"BTP1\":0,\"SP4\":2557,\"SP3\":2740,\"PFSL\":331,\"PFSR\":260,\"CR1\":4102,\"JUC\":101871,\"CFT\":0,\"14T\":0,\"12T\":2,\"BTP3\":45946,\"DIP\":2690,\"BTP2\":48042,\"SP5\":2414,\"AP2\":0,\"MSP2\":22,\"BAGR\":0},{\"Shift\":\"08-C\",\"CR3\":2354,\"MSP1\":0,\"BTP1\":0,\"SP4\":2208,\"SP3\":2209,\"PFSL\":4015,\"PFSR\":3689,\"CR1\":0,\"JUC\":100130,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":47434,\"DIP\":3766,\"BTP2\":48206,\"SP5\":1944,\"AP2\":0,\"MSP2\":53,\"BAGR\":0},{\"Shift\":\"08-Day\",\"CR3\":6203,\"MSP1\":0,\"BTP1\":0,\"SP4\":6881,\"SP3\":9528,\"PFSL\":6082,\"PFSR\":5592,\"CR1\":5592,\"JUC\":279227,\"CFT\":0,\"14T\":0,\"12T\":5,\"BTP3\":135431,\"DIP\":7306,\"BTP2\":137763,\"SP5\":8085,\"AP2\":0,\"MSP2\":75,\"BAGR\":0}],\"querys3\":[{\"Hour\":\"14-15\",\"CR3\":239,\"MSP1\":0,\"BTP\":0,\"SP4\":6,\"SP3\":728,\"PFSL\":0,\"PFSR\":0,\"CR1\":1005,\"JUC\":8599,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":4679,\"DIP\":0,\"BTP2\":4911,\"SP5\":12,\"AP2\":0,\"MSP2\":1,\"BAGR\":0},{\"Hour\":\"15-16\",\"CR3\":343,\"MSP1\":0,\"BTP\":0,\"SP4\":96,\"SP3\":883,\"PFSL\":0,\"PFSR\":0,\"CR1\":955,\"JUC\":13770,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6599,\"DIP\":675,\"BTP2\":6599,\"SP5\":971,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"16-17\",\"CR3\":343,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":300,\"PFSL\":0,\"PFSR\":0,\"CR1\":664,\"JUC\":14399,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":4010,\"DIP\":767,\"BTP2\":5962,\"SP5\":239,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"17-18\",\"CR3\":343,\"MSP1\":0,\"BTP\":0,\"SP4\":244,\"SP3\":732,\"PFSL\":0,\"PFSR\":0,\"CR1\":698,\"JUC\":13100,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6347,\"DIP\":1043,\"BTP2\":6371,\"SP5\":794,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"18-19\",\"CR3\":13,\"MSP1\":0,\"BTP\":0,\"SP4\":1101,\"SP3\":97,\"PFSL\":0,\"PFSR\":0,\"CR1\":369,\"JUC\":14323,\"CFT\":0,\"14T\":0,\"12T\":2,\"BTP3\":5824,\"DIP\":203,\"BTP2\":5886,\"SP5\":398,\"AP2\":0,\"MSP2\":5,\"BAGR\":0},{\"Hour\":\"19-20\",\"CR3\":0,\"MSP1\":0,\"BTP\":0,\"SP4\":683,\"SP3\":0,\"PFSL\":14,\"PFSR\":12,\"CR1\":391,\"JUC\":13433,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6599,\"DIP\":0,\"BTP2\":6383,\"SP5\":0,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"20-21\",\"CR3\":266,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":0,\"PFSL\":0,\"PFSR\":0,\"CR1\":0,\"JUC\":10969,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":5701,\"DIP\":0,\"BTP2\":5822,\"SP5\":0,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"21-22\",\"CR3\":332,\"MSP1\":0,\"BTP\":0,\"SP4\":427,\"SP3\":0,\"PFSL\":317,\"PFSR\":248,\"CR1\":1,\"JUC\":13258,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6247,\"DIP\":0,\"BTP2\":6218,\"SP5\":0,\"AP2\":0,\"MSP2\":16,\"BAGR\":0},{\"Hour\":\"08--B\",\"CR3\":1879,\"MSP1\":0,\"BTP\":0,\"SP4\":2557,\"SP3\":2740,\"PFSL\":331,\"PFSR\":260,\"CR1\":4083,\"JUC\":101851,\"CFT\":0,\"14T\":0,\"12T\":2,\"BTP3\":46006,\"DIP\":2688,\"BTP2\":48152,\"SP5\":2414,\"AP2\":0,\"MSP2\":22,\"BAGR\":0}],\"querys2\":[{\"Hour\":\"06-07\",\"CR3\":0,\"MSP1\":0,\"BTP\":0,\"SP4\":537,\"SP3\":519,\"PFSL\":147,\"PFSR\":121,\"CR1\":7,\"JUC\":0,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":1922,\"DIP\":103,\"BTP2\":1347,\"SP5\":158,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"07-08\",\"CR3\":93,\"MSP1\":0,\"BTP\":0,\"SP4\":459,\"SP3\":982,\"PFSL\":631,\"PFSR\":625,\"CR1\":0,\"JUC\":5914,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6599,\"DIP\":206,\"BTP2\":4589,\"SP5\":672,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"08-09\",\"CR3\":280,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":46,\"PFSL\":0,\"PFSR\":0,\"CR1\":0,\"JUC\":10219,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6541,\"DIP\":342,\"BTP2\":6588,\"SP5\":0,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"09-10\",\"CR3\":335,\"MSP1\":0,\"BTP\":0,\"SP4\":1039,\"SP3\":1020,\"PFSL\":799,\"PFSR\":771,\"CR1\":323,\"JUC\":11929,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6261,\"DIP\":0,\"BTP2\":6599,\"SP5\":672,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"10-11\",\"CR3\":337,\"MSP1\":0,\"BTP\":0,\"SP4\":81,\"SP3\":142,\"PFSL\":159,\"PFSR\":126,\"CR1\":522,\"JUC\":9746,\"CFT\":0,\"14T\":0,\"12T\":1,\"BTP3\":5208,\"DIP\":0,\"BTP2\":6599,\"SP5\":786,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"11-12\",\"CR3\":332,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":900,\"PFSL\":0,\"PFSR\":0,\"CR1\":46,\"JUC\":14931,\"CFT\":0,\"14T\":0,\"12T\":2,\"BTP3\":5241,\"DIP\":4,\"BTP2\":5476,\"SP5\":715,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"12-13\",\"CR3\":343,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":676,\"PFSL\":0,\"PFSR\":0,\"CR1\":171,\"JUC\":15173,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6313,\"DIP\":104,\"BTP2\":6589,\"SP5\":670,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"13-14\",\"CR3\":256,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":294,\"PFSL\":0,\"PFSR\":0,\"CR1\":440,\"JUC\":9584,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":3966,\"DIP\":93,\"BTP2\":3728,\"SP5\":54,\"AP2\":0,\"MSP2\":0,\"BAGR\":0},{\"Hour\":\"08--A\",\"CR3\":1976,\"MSP1\":0,\"BTP\":0,\"SP4\":2116,\"SP3\":4579,\"PFSL\":1736,\"PFSR\":1643,\"CR1\":1509,\"JUC\":77496,\"CFT\":0,\"14T\":0,\"12T\":3,\"BTP3\":42051,\"DIP\":852,\"BTP2\":41515,\"SP5\":3727,\"AP2\":0,\"MSP2\":0,\"BAGR\":0}],\"querys4\":[{\"Hour\":\"22-23\",\"CR3\":332,\"MSP1\":0,\"BTP\":0,\"SP4\":1010,\"SP3\":21,\"PFSL\":414,\"PFSR\":354,\"CR1\":0,\"JUC\":13207,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":3523,\"DIP\":0,\"BTP2\":4867,\"SP5\":0,\"AP2\":0,\"MSP2\":6,\"BAGR\":0},{\"Hour\":\"23-00\",\"CR3\":226,\"MSP1\":0,\"BTP\":0,\"SP4\":453,\"SP3\":0,\"PFSL\":920,\"PFSR\":831,\"CR1\":0,\"JUC\":14864,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6489,\"DIP\":0,\"BTP2\":6489,\"SP5\":0,\"AP2\":0,\"MSP2\":1,\"BAGR\":0},{\"Hour\":\"00-01\",\"CR3\":356,\"MSP1\":0,\"BTP\":0,\"SP4\":175,\"SP3\":6,\"PFSL\":113,\"PFSR\":124,\"CR1\":0,\"JUC\":14538,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":5812,\"DIP\":0,\"BTP2\":5794,\"SP5\":0,\"AP2\":0,\"MSP2\":5,\"BAGR\":0},{\"Hour\":\"01-02\",\"CR3\":330,\"MSP1\":0,\"BTP\":0,\"SP4\":467,\"SP3\":284,\"PFSL\":733,\"PFSR\":692,\"CR1\":0,\"JUC\":10407,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6593,\"DIP\":15,\"BTP2\":5661,\"SP5\":6,\"AP2\":0,\"MSP2\":17,\"BAGR\":0},{\"Hour\":\"02-03\",\"CR3\":254,\"MSP1\":0,\"BTP\":0,\"SP4\":96,\"SP3\":493,\"PFSL\":960,\"PFSR\":870,\"CR1\":0,\"JUC\":10168,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":5699,\"DIP\":994,\"BTP2\":6599,\"SP5\":815,\"AP2\":0,\"MSP2\":6,\"BAGR\":0},{\"Hour\":\"03-04\",\"CR3\":357,\"MSP1\":0,\"BTP\":0,\"SP4\":7,\"SP3\":0,\"PFSL\":18,\"PFSR\":15,\"CR1\":0,\"JUC\":14047,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6599,\"DIP\":1178,\"BTP2\":6599,\"SP5\":0,\"AP2\":0,\"MSP2\":4,\"BAGR\":0},{\"Hour\":\"04-05\",\"CR3\":195,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":469,\"PFSL\":339,\"PFSR\":316,\"CR1\":0,\"JUC\":10545,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6599,\"DIP\":732,\"BTP2\":6308,\"SP5\":633,\"AP2\":0,\"MSP2\":9,\"BAGR\":0},{\"Hour\":\"05-06\",\"CR3\":298,\"MSP1\":0,\"BTP\":0,\"SP4\":0,\"SP3\":936,\"PFSL\":518,\"PFSR\":487,\"CR1\":0,\"JUC\":12104,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":6060,\"DIP\":847,\"BTP2\":5779,\"SP5\":490,\"AP2\":0,\"MSP2\":5,\"BAGR\":0},{\"Hour\":\"08--C\",\"CR3\":2354,\"MSP1\":0,\"BTP\":0,\"SP4\":2208,\"SP3\":2209,\"PFSL\":4015,\"PFSR\":3689,\"CR1\":0,\"JUC\":100130,\"CFT\":0,\"14T\":0,\"12T\":0,\"BTP3\":47434,\"DIP\":3766,\"BTP2\":48206,\"SP5\":1944,\"AP2\":0,\"MSP2\":53,\"BAGR\":0}]}","tot":"json"},{"t":"set","p":"pdf_header1","pt":"flow","to":"Report from Old Method","tot":"str"},{"t":"set","p":"shifta","pt":"flow","to":"SHIFT-A","tot":"str"},{"t":"set","p":"shiftb","pt":"flow","to":"SHIFT-B","tot":"str"},{"t":"set","p":"shiftc","pt":"flow","to":"SHIFT-C","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":2820,"wires":[["1f3a0c4b25900303","3b65129d5fc39fba"]]},{"id":"b682b9984edb4eeb","type":"inject","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"Load","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":250,"y":2820,"wires":[["e7acbd52452fd432","19f1d07d833c4089"]]},{"id":"1f3a0c4b25900303","type":"function","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"data to pdf","func":"///\nlet querys1Data = []\nmsg.payload.querys1.forEach(obj => { querys1Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true, alignment: 'left' } })) })\nlet querys2Data = []\nmsg.payload.querys2.forEach(obj => { querys2Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\nlet querys3Data = []\nmsg.payload.querys3.forEach(obj => { querys3Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\nlet querys4Data = []\nmsg.payload.querys4.forEach(obj => { querys4Data.push(Object.entries(obj).map(([key, value]) => { return !isNaN(value) ? { text: value, alignment: 'right' } : { text: value, bold: true,alignment: 'left' } })) })\n\n///\n\nlet tableData = {\n\n    // pageSize: 'a3',\n    pageSize: {    width: 1100,    height: 'auto'    },    pageOrientation: 'portrait',\n\n    content: [\n        { text: flow.get(\"pdf_header1\"), fontSize: 20, bold: true, alignment: 'center', color: 'green', margin: [12, 0, 12, 0] },\n        { text: ' ', fontSize: 20, bold: true, margin: [0, 0, 0, 0] },\n\n        { text: flow.get(\"production_header\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },        \n    {\n                        style: 'tableExample',\n            table: {\n                headerRows: 1,\n//                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys1[0]).map(key => { return { text: key, alignment: key.startsWith(\"Shift\") ? \"left\" : \"right\", style: 'tableHeader' } }),\n                    ...querys1Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n        {text: '---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------', \nfontSize: 10, bold: false, color: 'orange', margin: [0, 0, 0, 5] },\n\n        { text: flow.get(\"shifta\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n//                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys2[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\", style: 'tableHeader' } }),\n                    ...querys2Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        { text: flow.get(\"shiftb\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n//                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys3[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\",style: 'tableHeader' } }),\n                    ...querys3Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        { text: flow.get(\"shiftc\"), fontSize: 14, bold: true, margin: [12, 5, 12, 5] },\n        \n        {\n            style: 'tableExample',\n            table: {\n                headerRows: 1,\n//                widths: [40, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35, 35],\n                body: [\n                    Object.keys(msg.payload.querys4[0]).map(key => { return { text: key, alignment: key.startsWith(\"Hour\") ? \"left\" : \"right\",style: 'tableHeader' } }),\n                    ...querys4Data\n                ]\n            },\n            layout: 'lightHorizontalLines'\n        },\n\n        \n        \n    ],\n    \n\n    styles: {\n        header: {\n            fontSize: 12,\n            bold: true,\n            margin: [12, 2, 12, 2]\n        },\n        subheader: {\n            fontSize: 12,\n            bold: true,\n            margin: [12, 2, 12, 2]\n        },\n        tableExample: {\n            fontSize: 10,\n            bold: false,\n        },\n        tableHeader: {\n            bold: true,\n            fontSize: 12,\n            color: 'red'\n        }\n    },\n    defaultStyle: {\n        // alignment: 'justify'\n        }\n\n};\n\nmsg.payload = tableData;\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":910,"y":2760,"wires":[["cbb706cb5d296bde","351f4a478470d01a"]]},{"id":"3b65129d5fc39fba","type":"debug","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"Old","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":2820,"wires":[]},{"id":"19f1d07d833c4089","type":"debug","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"Old-Raw","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":330,"y":2880,"wires":[]},{"id":"b2d12f4267a996af","type":"change","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"PRODUCTION REPORT (NEW)","rules":[{"t":"set","p":"pdf_header1","pt":"flow","to":"Report from New Method","tot":"str"},{"t":"set","p":"shifta","pt":"flow","to":"SHIFT-1","tot":"str"},{"t":"set","p":"shiftb","pt":"flow","to":"SHIFT-2","tot":"str"},{"t":"set","p":"shiftc","pt":"flow","to":"SHIFT-3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":2700,"wires":[["1f3a0c4b25900303","a14e0509d666af81"]]},{"id":"cbb706cb5d296bde","type":"debug","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"Check","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1070,"y":2700,"wires":[]},{"id":"351f4a478470d01a","type":"pdfmake","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"","outputType":"Buffer","inputProperty":"payload","options":"{}","outputProperty":"payload","x":1120,"y":2760,"wires":[["1aace55de5fe6514"]]},{"id":"5a0e6d1cd6577bbf","type":"change","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"Lookup","rules":[{"t":"set","p":"lookup","pt":"msg","to":"{\"querys1\":[\"HA\",\"HB\",\"HC\",\"HD\"],\"querys2\":[\"H06\",\"H07\",\"H08\",\"H09\",\"H10\",\"H11\",\"H12\",\"H13\",\"HA\"],\"querys3\":[\"H14\",\"H15\",\"H16\",\"H17\",\"H18\",\"H19\",\"H20\",\"H21\",\"HB\"],\"querys4\":[\"H22\",\"H23\",\"H00\",\"H01\",\"H02\",\"H03\",\"H04\",\"H05\",\"HC\"]}","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"$merge(\t    $each($$.lookup, function($v,$k){\t        {\t            $k:[$v.$single($$.payload, function($vo,$io){\t            $vo.datehour = $\t            })]\t        }\t    })\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":2700,"wires":[["b2d12f4267a996af"]]},{"id":"a14e0509d666af81","type":"debug","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"New","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":2680,"wires":[]},{"id":"1aace55de5fe6514","type":"change","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"","rules":[{"t":"set","p":"fn","pt":"flow","to":"c:\\temp\\prd.pdf","tot":"str"},{"t":"set","p":"fn","pt":"msg","to":"fn","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":1300,"y":2760,"wires":[["c2841fd1eca9125a"]]},{"id":"82f86fcf50667ffb","type":"inject","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H06\",\"sum_m00\":0,\"sum_m01\":2,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":549,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":7578,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":55,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":378,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":12,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H07\",\"sum_m00\":0,\"sum_m01\":3,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":122,\"sum_m05\":330,\"sum_m06\":293,\"sum_m07\":0,\"sum_m08\":14366,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":1156,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1858,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":6,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H08\",\"sum_m00\":0,\"sum_m01\":2,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":110,\"sum_m06\":101,\"sum_m07\":0,\"sum_m08\":14277,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":1014,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1831,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":15,\"sum_m20\":0,\"sum_m21\":506,\"sum_m22\":432,\"sum_m23\":0},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H09\",\"sum_m00\":0,\"sum_m01\":27,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1014,\"sum_m05\":456,\"sum_m06\":418,\"sum_m07\":0,\"sum_m08\":11719,\"sum_m09\":0,\"sum_m10\":2,\"sum_m11\":966,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":568,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":926,\"sum_m22\":955,\"sum_m23\":0},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H10\",\"sum_m00\":0,\"sum_m01\":2,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":508,\"sum_m05\":1118,\"sum_m06\":969,\"sum_m07\":0,\"sum_m08\":2102,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":1361,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":465,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":47,\"sum_m20\":0,\"sum_m21\":597,\"sum_m22\":458,\"sum_m23\":0},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H11\",\"sum_m00\":0,\"sum_m01\":76,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":509,\"sum_m05\":950,\"sum_m06\":838,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":502,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1850,\"sum_m17\":0,\"sum_m18\":5,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":132,\"sum_m22\":107,\"sum_m23\":5},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H12\",\"sum_m00\":0,\"sum_m01\":402,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":510,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":1091,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1821,\"sum_m17\":0,\"sum_m18\":14,\"sum_m19\":53,\"sum_m20\":0,\"sum_m21\":106,\"sum_m22\":16,\"sum_m23\":633},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"H13\",\"sum_m00\":0,\"sum_m01\":943,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":551,\"sum_m06\":491,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":238,\"sum_m11\":1082,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1249,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":45,\"sum_m20\":0,\"sum_m21\":17,\"sum_m22\":0,\"sum_m23\":643},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H14\",\"sum_m00\":0,\"sum_m01\":1022,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":538,\"sum_m05\":402,\"sum_m06\":357,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":1137,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1681,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":35,\"sum_m20\":2,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":573},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H15\",\"sum_m00\":0,\"sum_m01\":1598,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":636,\"sum_m05\":918,\"sum_m06\":787,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":870,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1751,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":51,\"sum_m20\":270,\"sum_m21\":36,\"sum_m22\":7,\"sum_m23\":67},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H16\",\"sum_m00\":0,\"sum_m01\":1545,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":7,\"sum_m06\":5,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":0,\"sum_m11\":104,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2094,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":37,\"sum_m20\":297,\"sum_m21\":0,\"sum_m22\":0,\"sum_m23\":0},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H17\",\"sum_m00\":0,\"sum_m01\":890,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":522,\"sum_m06\":412,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":171,\"sum_m11\":356,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1574,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":60,\"sum_m20\":258,\"sum_m21\":621,\"sum_m22\":331,\"sum_m23\":138},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H18\",\"sum_m00\":0,\"sum_m01\":937,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":887,\"sum_m06\":702,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":180,\"sum_m11\":997,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1575,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":44,\"sum_m20\":0,\"sum_m21\":0,\"sum_m22\":140,\"sum_m23\":1112},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H19\",\"sum_m00\":0,\"sum_m01\":1736,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":1277,\"sum_m11\":1102,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1478,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":41,\"sum_m20\":435,\"sum_m21\":670,\"sum_m22\":1954,\"sum_m23\":1010},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H20\",\"sum_m00\":0,\"sum_m01\":1714,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":1729,\"sum_m11\":649,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1986,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":53,\"sum_m20\":350,\"sum_m21\":4317,\"sum_m22\":5046,\"sum_m23\":1109},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"H21\",\"sum_m00\":0,\"sum_m01\":1723,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":1251,\"sum_m11\":963,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1621,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":44,\"sum_m20\":358,\"sum_m21\":3176,\"sum_m22\":3587,\"sum_m23\":999},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H22\",\"sum_m00\":0,\"sum_m01\":853,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":527,\"sum_m11\":220,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1253,\"sum_m17\":0,\"sum_m18\":409,\"sum_m19\":60,\"sum_m20\":499,\"sum_m21\":5399,\"sum_m22\":5399,\"sum_m23\":1022},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H23\",\"sum_m00\":0,\"sum_m01\":933,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":115,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":664,\"sum_m11\":789,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1161,\"sum_m17\":0,\"sum_m18\":1096,\"sum_m19\":60,\"sum_m20\":304,\"sum_m21\":5400,\"sum_m22\":5399,\"sum_m23\":1176},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H00\",\"sum_m00\":0,\"sum_m01\":1647,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":709,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":66,\"sum_m09\":0,\"sum_m10\":940,\"sum_m11\":701,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1925,\"sum_m17\":0,\"sum_m18\":452,\"sum_m19\":57,\"sum_m20\":414,\"sum_m21\":4739,\"sum_m22\":4545,\"sum_m23\":1167},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H01\",\"sum_m00\":0,\"sum_m01\":1663,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1352,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":1381,\"sum_m09\":0,\"sum_m10\":2908,\"sum_m11\":774,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":2094,\"sum_m17\":0,\"sum_m18\":952,\"sum_m19\":60,\"sum_m20\":80,\"sum_m21\":5399,\"sum_m22\":5399,\"sum_m23\":1043},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H02\",\"sum_m00\":0,\"sum_m01\":1573,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1161,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":13067,\"sum_m09\":0,\"sum_m10\":3078,\"sum_m11\":1244,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1680,\"sum_m17\":0,\"sum_m18\":650,\"sum_m19\":59,\"sum_m20\":561,\"sum_m21\":5400,\"sum_m22\":5399,\"sum_m23\":1174},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H03\",\"sum_m00\":0,\"sum_m01\":439,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":0,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":12657,\"sum_m09\":0,\"sum_m10\":220,\"sum_m11\":55,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1244,\"sum_m17\":0,\"sum_m18\":519,\"sum_m19\":60,\"sum_m20\":0,\"sum_m21\":5399,\"sum_m22\":4977,\"sum_m23\":1171},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H04\",\"sum_m00\":0,\"sum_m01\":1459,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":992,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":12296,\"sum_m09\":0,\"sum_m10\":1665,\"sum_m11\":1100,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1652,\"sum_m17\":0,\"sum_m18\":928,\"sum_m19\":60,\"sum_m20\":447,\"sum_m21\":4765,\"sum_m22\":4645,\"sum_m23\":1152},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"H05\",\"sum_m00\":0,\"sum_m01\":1464,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":980,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":13769,\"sum_m09\":0,\"sum_m10\":1465,\"sum_m11\":871,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":1234,\"sum_m17\":0,\"sum_m18\":1189,\"sum_m19\":60,\"sum_m20\":464,\"sum_m21\":4507,\"sum_m22\":5177,\"sum_m23\":1094},{\"dateshift\":\"10-Jan-A\",\"datehour\":\"HA\",\"sum_m00\":0,\"sum_m01\":1457,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":3212,\"sum_m05\":3515,\"sum_m06\":3110,\"sum_m07\":0,\"sum_m08\":50042,\"sum_m09\":0,\"sum_m10\":240,\"sum_m11\":7227,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":10020,\"sum_m17\":0,\"sum_m18\":19,\"sum_m19\":298,\"sum_m20\":0,\"sum_m21\":2284,\"sum_m22\":1968,\"sum_m23\":1281},{\"dateshift\":\"10-Jan-B\",\"datehour\":\"HB\",\"sum_m00\":0,\"sum_m01\":11165,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":1174,\"sum_m05\":2736,\"sum_m06\":2263,\"sum_m07\":0,\"sum_m08\":0,\"sum_m09\":0,\"sum_m10\":4608,\"sum_m11\":6178,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":13760,\"sum_m17\":0,\"sum_m18\":0,\"sum_m19\":365,\"sum_m20\":1970,\"sum_m21\":8820,\"sum_m22\":11065,\"sum_m23\":5008},{\"dateshift\":\"10-Jan-C\",\"datehour\":\"HC\",\"sum_m00\":0,\"sum_m01\":10031,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":5309,\"sum_m05\":0,\"sum_m06\":0,\"sum_m07\":0,\"sum_m08\":53236,\"sum_m09\":0,\"sum_m10\":11467,\"sum_m11\":5754,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":12243,\"sum_m17\":0,\"sum_m18\":6195,\"sum_m19\":476,\"sum_m20\":2769,\"sum_m21\":41008,\"sum_m22\":40940,\"sum_m23\":8999},{\"dateshift\":\"10-Jan-Day\",\"datehour\":\"HD\",\"sum_m00\":0,\"sum_m01\":22653,\"sum_m02\":0,\"sum_m03\":0,\"sum_m04\":9695,\"sum_m05\":6251,\"sum_m06\":5373,\"sum_m07\":0,\"sum_m08\":103278,\"sum_m09\":0,\"sum_m10\":16315,\"sum_m11\":19159,\"sum_m12\":0,\"sum_m13\":0,\"sum_m14\":0,\"sum_m15\":0,\"sum_m16\":36023,\"sum_m17\":0,\"sum_m18\":6214,\"sum_m19\":1139,\"sum_m20\":4739,\"sum_m21\":52112,\"sum_m22\":53973,\"sum_m23\":15288}]","payloadType":"json","x":250,"y":2700,"wires":[["5a0e6d1cd6577bbf","b7d5e5bb0c82fbd7"]]},{"id":"c2841fd1eca9125a","type":"file","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"","filename":"fn","filenameType":"msg","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":1415,"y":2760,"wires":[[]],"l":false},{"id":"b7d5e5bb0c82fbd7","type":"debug","z":"37ef9ab5d60ffd43","g":"ec11c4c479035a8b","name":"New-Raw","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":330,"y":2640,"wires":[]}]

Thanks @E1cid for the patient hearing and giving me a concise and effective solution.
although , the new report has many extra elements which i dont want in the report, column headers are not descriptive and also the widths of each column is not optimised, but thats for another topic. current problem at hand solved!

h

Topic		Replies	Views
Transpose the Object/Array General	9	269	28 December 2022
Convert array values to array of objects General	3	367	10 November 2022
Mapping a specific data of an array of properties in JSON General	10	1332	24 April 2021
Transform data from array General	11	441	9 January 2023
Help Parsing Data with JSONata General	13	2418	11 March 2019

Arrays/Objects/Conversion

Related topics