COVID-19 status by country

Hi,

I am not really satisfied with the "COVID overviews by country" I can find on the internet.
(the best one I think = https://www.worldometers.info/coronavirus/#countries )

They fail to properly reflect the current situation and how it is evolving in the last weeks.
The issues they have is:

  • give absolute numbers instead of relative numbers in terms of population size
  • give day statistics which are fluctuating a lot during a week (e.g. less testing and reporting is done during the weekend)
  • don't give a proper indication of the current situation
  • don't make it clear how it is evolving over the last couple of weeks
  • don't make it easy to properly compare between countries

Following flow/project tries to address these points:

[{"id":"3739886a.1825b8","type":"tab","label":"COVID19 - country comparison","disabled":false,"info":""},{"id":"48cc765f.3e0de8","type":"http request","z":"3739886a.1825b8","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv","tls":"","persist":false,"proxy":"","authType":"","x":250,"y":80,"wires":[["d78d0310.f64ee","b1fdd2fb.c9e67"]]},{"id":"d78d0310.f64ee","type":"debug","z":"3739886a.1825b8","name":"http response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":580,"y":80,"wires":[]},{"id":"b1fdd2fb.c9e67","type":"csv","z":"3739886a.1825b8","name":"","sep":",","hdrin":false,"hdrout":true,"multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"x":230,"y":140,"wires":[["79857f5e.29c53","d78c600d.35a92"]]},{"id":"79857f5e.29c53","type":"debug","z":"3739886a.1825b8","name":"csv2array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":560,"y":140,"wires":[]},{"id":"d78c600d.35a92","type":"change","z":"3739886a.1825b8","name":"calculate reproduction rates,...","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* payload[0]~>$sift(function($v,$k){ $k~>/col/}) */\t(\t   $header := payload[0]~>$each(function($v,$k) { { \"key\" : $k, \"val\" : $v} } );\t   {\t     \"header\" : $header,\t     \"last_date_retrieved\" : $header[-1].val,\t     \"last_col_min_8\" : $header[-8],\t     \"location\" : payload[col2 != \"Country/Region\"].(\t        $new_cases_current   := ($lookup($,$header[-1].key)-$lookup($,$header[-8].key))/7;\t        $new_cases_1day_ago  := ($lookup($,$header[-2].key)-$lookup($,$header[-9].key))/7; \t        $new_cases_8days_ago  := ($lookup($,$header[-9].key)-$lookup($,$header[-16].key))/7;     \t        $new_cases_1week_ago := ($lookup($,$header[-8].key)-$lookup($,$header[-15].key))/7;\t        $new_cases_2week_ago := ($lookup($,$header[-15].key)-$lookup($,$header[-22].key))/7;\t        $new_cases_3week_ago := ($lookup($,$header[-22].key)-$lookup($,$header[-29].key))/7;    \t        $new_cases_4week_ago := ($lookup($,$header[-29].key)-$lookup($,$header[-36].key))/7; \t        {\t         \"country_region\" : $lookup($,\"col2\"),\t         \"province_state\" : $lookup($,\"col1\"),\t         \"total_conf_cases\": $lookup($,$header[-1].key),\t         \"new_cases_current\" : $new_cases_current,\t         \"new_cases_1week_ago\" : $new_cases_1week_ago,\t         \"reproduction_number\" :   $new_cases_1week_ago = 0 \t                                   ? ($new_cases_current = 0 ? 0 : 9999)\t                                   : ($new_cases_current/$new_cases_1week_ago)~>$round(2),\t         \"reproduction_number_1day_ago\" : $new_cases_8days_ago = 0 \t                                          ? ($new_cases_8days_ago = 0 ? 0 : 9999)\t                                          : ($new_cases_1day_ago/$new_cases_8days_ago)~>$round(2),\t         \"reproduction_number_1week_ago\" :   $new_cases_2week_ago = 0 \t                                             ? ($new_cases_1week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_1week_ago/$new_cases_2week_ago)~>$round(2),\t         \"reproduction_number_2week_ago\" :   $new_cases_3week_ago = 0 \t                                             ? ($new_cases_2week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_2week_ago/$new_cases_3week_ago)~>$round(2),  \t         \"reproduction_number_3week_ago\" :   $new_cases_4week_ago = 0 \t                                             ? ($new_cases_3week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_3week_ago/$new_cases_4week_ago)~>$round(2)\t        }\t      )\t   }\t   \t)","tot":"jsonata"},{"t":"set","p":"last_date_retrieved","pt":"msg","to":"payload.last_date_retrieved","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":200,"wires":[["9124a122.24074","4e81e87d.b3abf8"]]},{"id":"9124a122.24074","type":"debug","z":"3739886a.1825b8","name":"with reproduction rates,...","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":610,"y":200,"wires":[]},{"id":"4e81e87d.b3abf8","type":"change","z":"3739886a.1825b8","name":"select only countries","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.location[$not($exists(province_state))]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":260,"wires":[["4406d2a6.88b6ac","7f27f9ea.a5ea88"]]},{"id":"4406d2a6.88b6ac","type":"debug","z":"3739886a.1825b8","name":"only countries","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":580,"y":260,"wires":[]},{"id":"e9092edc.7f8d3","type":"ui_table","z":"3739886a.1825b8","group":"984606ac.155a48","name":"","order":0,"width":"28","height":"15","columns":[{"field":"country_region","title":"location","width":"16px","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"new_cases_per_million","title":"cases/day/milion","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number","title":"RN (Repr. Nr)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1day_ago","title":"RN (-1 day)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1week_ago","title":"RN  (-1week)","width":"7px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_2week_ago","title":"RN (-2week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_3week_ago","title":"RN (-3week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"total_cases_per_million","title":"tot. cases/million","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"population_in_million","title":"popul. [million]","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"total_conf_cases","title":"tot.  cases","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":230,"y":440,"wires":[]},{"id":"7f27f9ea.a5ea88","type":"change","z":"3739886a.1825b8","name":"add population, ...","rules":[{"t":"set","p":"countries","pt":"msg","to":"[{\"name\":\"Burundi\",\"population\":11890.781},{\"name\":\"Comoros\",\"population\":869.595},{\"name\":\"Djibouti\",\"population\":988.002},{\"name\":\"Eritrea\",\"population\":3546.427},{\"name\":\"Ethiopia\",\"population\":114963.583},{\"name\":\"Kenya\",\"population\":53771.3},{\"name\":\"Madagascar\",\"population\":27691.019},{\"name\":\"Malawi\",\"population\":19129.955},{\"name\":\"Mauritius\",\"population\":1271.767},{\"name\":\"Mayotte\",\"population\":272.813},{\"name\":\"Mozambique\",\"population\":31255.435},{\"name\":\"Réunion\",\"population\":895.308},{\"name\":\"Rwanda\",\"population\":12952.209},{\"name\":\"Seychelles\",\"population\":98.34},{\"name\":\"Somalia\",\"population\":15893.219},{\"name\":\"South Sudan\",\"population\":11193.729},{\"name\":\"Uganda\",\"population\":45741},{\"name\":\"Tanzania\",\"population\":59734.213},{\"name\":\"Zambia\",\"population\":18383.956},{\"name\":\"Zimbabwe\",\"population\":14862.927},{\"name\":\"Angola\",\"population\":32866.268},{\"name\":\"Cameroon\",\"population\":26545.864},{\"name\":\"Central African Republic\",\"population\":4829.764},{\"name\":\"Chad\",\"population\":16425.859},{\"name\":\"Congo (Brazzaville)\",\"population\":5518.092},{\"name\":\"Congo (Kinshasa)\",\"population\":89561.404},{\"name\":\"Equatorial Guinea\",\"population\":1402.985},{\"name\":\"Gabon\",\"population\":2225.728},{\"name\":\"Sao Tome and Principe\",\"population\":219.161},{\"name\":\"Botswana\",\"population\":2351.625},{\"name\":\"Eswatini\",\"population\":1160.164},{\"name\":\"Lesotho\",\"population\":2142.252},{\"name\":\"Namibia\",\"population\":2540.916},{\"name\":\"South Africa\",\"population\":59308.69},{\"name\":\"Benin\",\"population\":12123.198},{\"name\":\"Burkina Faso\",\"population\":20903.278},{\"name\":\"Cabo Verde\",\"population\":555.988},{\"name\":\"Cote d'Ivoire\",\"population\":26378.275},{\"name\":\"Gambia\",\"population\":2416.664},{\"name\":\"Ghana\",\"population\":31072.945},{\"name\":\"Guinea\",\"population\":13132.792},{\"name\":\"Guinea-Bissau\",\"population\":1967.998},{\"name\":\"Liberia\",\"population\":5057.677},{\"name\":\"Mali\",\"population\":20250.834},{\"name\":\"Mauritania\",\"population\":4649.66},{\"name\":\"Niger\",\"population\":24206.636},{\"name\":\"Nigeria\",\"population\":206139.587},{\"name\":\"Saint Helena\",\"population\":6.071},{\"name\":\"Senegal\",\"population\":16743.93},{\"name\":\"Sierra Leone\",\"population\":7976.985},{\"name\":\"Togo\",\"population\":8278.737},{\"name\":\"Algeria\",\"population\":43851.043},{\"name\":\"Egypt\",\"population\":102334.403},{\"name\":\"Libya\",\"population\":6871.287},{\"name\":\"Morocco\",\"population\":36910.558},{\"name\":\"Sudan\",\"population\":43849.269},{\"name\":\"Tunisia\",\"population\":11818.618},{\"name\":\"Western Sahara\",\"population\":597.33},{\"name\":\"Armenia\",\"population\":2963.234},{\"name\":\"Azerbaijan\",\"population\":10139.175},{\"name\":\"Bahrain\",\"population\":1701.583},{\"name\":\"Cyprus\",\"population\":1207.361},{\"name\":\"Georgia\",\"population\":3989.175},{\"name\":\"Iraq\",\"population\":40222.503},{\"name\":\"Israel\",\"population\":8655.541},{\"name\":\"Jordan\",\"population\":10203.14},{\"name\":\"Kuwait\",\"population\":4270.563},{\"name\":\"Lebanon\",\"population\":6825.442},{\"name\":\"Oman\",\"population\":5106.622},{\"name\":\"Qatar\",\"population\":2881.06},{\"name\":\"Saudi Arabia\",\"population\":34813.867},{\"name\":\"State of Palestine\",\"population\":5101.416},{\"name\":\"Syria\",\"population\":17500.657},{\"name\":\"Turkey\",\"population\":84339.067},{\"name\":\"United Arab Emirates\",\"population\":9890.4},{\"name\":\"Yemen\",\"population\":29825.968},{\"name\":\"Kazakhstan\",\"population\":18776.707},{\"name\":\"Kyrgyzstan\",\"population\":6524.191},{\"name\":\"Tajikistan\",\"population\":9537.642},{\"name\":\"Turkmenistan\",\"population\":6031.187},{\"name\":\"Uzbekistan\",\"population\":33469.199},{\"name\":\"Afghanistan\",\"population\":38928.341},{\"name\":\"Bangladesh\",\"population\":164689.383},{\"name\":\"Bhutan\",\"population\":771.612},{\"name\":\"India\",\"population\":1380004.385},{\"name\":\"Iran\",\"population\":83992.953},{\"name\":\"Maldives\",\"population\":540.542},{\"name\":\"Nepal\",\"population\":29136.808},{\"name\":\"Pakistan\",\"population\":220892.331},{\"name\":\"Sri Lanka\",\"population\":21413.25},{\"name\":\"China\",\"population\":1439323.774},{\"name\":\"China, Hong Kong SAR\",\"population\":7496.988},{\"name\":\"China, Macao SAR\",\"population\":649.342},{\"name\":\"Taiwan*\",\"population\":23816.775},{\"name\":\"Dem. People's Republic of Korea\",\"population\":25778.815},{\"name\":\"Japan\",\"population\":126476.458},{\"name\":\"Mongolia\",\"population\":3278.292},{\"name\":\"Korea, South\",\"population\":51269.183},{\"name\":\"Brunei\",\"population\":437.483},{\"name\":\"Cambodia\",\"population\":16718.971},{\"name\":\"Indonesia\",\"population\":273523.621},{\"name\":\"Laos\",\"population\":7275.556},{\"name\":\"Malaysia\",\"population\":32365.998},{\"name\":\"Myanmar\",\"population\":54409.794},{\"name\":\"Philippines\",\"population\":109581.085},{\"name\":\"Singapore\",\"population\":5850.343},{\"name\":\"Thailand\",\"population\":69799.978},{\"name\":\"Timor-Leste\",\"population\":1318.442},{\"name\":\"Vietnam\",\"population\":97338.583},{\"name\":\"Anguilla\",\"population\":15.002},{\"name\":\"Antigua and Barbuda\",\"population\":97.928},{\"name\":\"Aruba\",\"population\":106.766},{\"name\":\"Bahamas\",\"population\":393.248},{\"name\":\"Barbados\",\"population\":287.371},{\"name\":\"Bonaire, Sint Eustatius and Saba\",\"population\":26.221},{\"name\":\"British Virgin Islands\",\"population\":30.237},{\"name\":\"Cayman Islands\",\"population\":65.72},{\"name\":\"Cuba\",\"population\":11326.616},{\"name\":\"Curaçao\",\"population\":164.1},{\"name\":\"Dominica\",\"population\":71.991},{\"name\":\"Dominican Republic\",\"population\":10847.904},{\"name\":\"Grenada\",\"population\":112.519},{\"name\":\"Guadeloupe\",\"population\":400.127},{\"name\":\"Haiti\",\"population\":11402.533},{\"name\":\"Jamaica\",\"population\":2961.161},{\"name\":\"Martinique\",\"population\":375.265},{\"name\":\"Montserrat\",\"population\":4.999},{\"name\":\"Puerto Rico\",\"population\":2860.84},{\"name\":\"Saint Barthélem\",\"population\":9.885},{\"name\":\"Saint Kitts and Nevis\",\"population\":53.192},{\"name\":\"Saint Lucia\",\"population\":183.629},{\"name\":\"Saint Martin (French part)\",\"population\":38.659},{\"name\":\"Saint Vincent and the Grenadines\",\"population\":110.947},{\"name\":\"Sint Maarten (Dutch part)\",\"population\":42.882},{\"name\":\"Trinidad and Tobago\",\"population\":1399.491},{\"name\":\"Turks and Caicos Islands\",\"population\":38.718},{\"name\":\"United States Virgin Islands\",\"population\":104.423},{\"name\":\"Belize\",\"population\":397.621},{\"name\":\"Costa Rica\",\"population\":5094.114},{\"name\":\"El Salvador\",\"population\":6486.201},{\"name\":\"Guatemala\",\"population\":17915.567},{\"name\":\"Honduras\",\"population\":9904.608},{\"name\":\"Mexico\",\"population\":128932.753},{\"name\":\"Nicaragua\",\"population\":6624.554},{\"name\":\"Panama\",\"population\":4314.768},{\"name\":\"Argentina\",\"population\":45195.777},{\"name\":\"Bolivia\",\"population\":11673.029},{\"name\":\"Brazil\",\"population\":212559.409},{\"name\":\"Chile\",\"population\":19116.209},{\"name\":\"Colombia\",\"population\":50882.884},{\"name\":\"Ecuador\",\"population\":17643.06},{\"name\":\"Falkland Islands (Malvinas)\",\"population\":3.483},{\"name\":\"French Guiana\",\"population\":298.682},{\"name\":\"Guyana\",\"population\":786.559},{\"name\":\"Paraguay\",\"population\":7132.53},{\"name\":\"Peru\",\"population\":32971.846},{\"name\":\"Suriname\",\"population\":586.634},{\"name\":\"Uruguay\",\"population\":3473.727},{\"name\":\"Venezuela\",\"population\":28435.943},{\"name\":\"Australia\",\"population\":25499.881},{\"name\":\"New Zealand\",\"population\":4822.233},{\"name\":\"Fiji\",\"population\":896.444},{\"name\":\"New Caledonia\",\"population\":285.491},{\"name\":\"Papua New Guinea\",\"population\":8947.027},{\"name\":\"Solomon Islands\",\"population\":686.878},{\"name\":\"Vanuatu\",\"population\":307.15},{\"name\":\"Guam\",\"population\":168.783},{\"name\":\"Kiribati\",\"population\":119.446},{\"name\":\"Marshall Islands\",\"population\":59.194},{\"name\":\"Micronesia (Fed. States of)\",\"population\":115.021},{\"name\":\"Nauru\",\"population\":10.834},{\"name\":\"Northern Mariana Islands\",\"population\":57.557},{\"name\":\"Palau\",\"population\":18.092},{\"name\":\"American Samoa\",\"population\":55.197},{\"name\":\"Cook Islands\",\"population\":17.564},{\"name\":\"French Polynesia\",\"population\":280.904},{\"name\":\"Niue\",\"population\":1.618},{\"name\":\"Samoa\",\"population\":198.41},{\"name\":\"Tokelau\",\"population\":1.35},{\"name\":\"Tonga\",\"population\":105.697},{\"name\":\"Tuvalu\",\"population\":11.792},{\"name\":\"Wallis and Futuna Islands\",\"population\":11.246},{\"name\":\"Belarus\",\"population\":9449.321},{\"name\":\"Bulgaria\",\"population\":6948.445},{\"name\":\"Czechia\",\"population\":10708.982},{\"name\":\"Hungary\",\"population\":9660.35},{\"name\":\"Poland\",\"population\":37846.605},{\"name\":\"Moldova\",\"population\":4033.963},{\"name\":\"Romania\",\"population\":19237.682},{\"name\":\"Russia\",\"population\":145934.46},{\"name\":\"Slovakia\",\"population\":5459.643},{\"name\":\"Ukraine\",\"population\":43733.759},{\"name\":\"Channel Islands\",\"population\":173.859},{\"name\":\"Denmark\",\"population\":5792.203},{\"name\":\"Estonia\",\"population\":1326.539},{\"name\":\"Faroe Islands\",\"population\":48.865},{\"name\":\"Finland\",\"population\":5540.718},{\"name\":\"Iceland\",\"population\":341.25},{\"name\":\"Ireland\",\"population\":4937.796},{\"name\":\"Isle of Man\",\"population\":85.032},{\"name\":\"Latvia\",\"population\":1886.202},{\"name\":\"Lithuania\",\"population\":2722.291},{\"name\":\"Norway\",\"population\":5421.242},{\"name\":\"Sweden\",\"population\":10099.27},{\"name\":\"United Kingdom\",\"population\":67886.004},{\"name\":\"Albania\",\"population\":2877.8},{\"name\":\"Andorra\",\"population\":77.265},{\"name\":\"Bosnia and Herzegovina\",\"population\":3280.815},{\"name\":\"Croatia\",\"population\":4105.268},{\"name\":\"Gibraltar\",\"population\":33.691},{\"name\":\"Greece\",\"population\":10423.056},{\"name\":\"Holy See\",\"population\":0.809},{\"name\":\"Italy\",\"population\":60461.828},{\"name\":\"Malta\",\"population\":441.539},{\"name\":\"Montenegro\",\"population\":628.062},{\"name\":\"North Macedonia\",\"population\":2083.38},{\"name\":\"Portugal\",\"population\":10196.707},{\"name\":\"San Marino\",\"population\":33.938},{\"name\":\"Serbia\",\"population\":8737.37},{\"name\":\"Slovenia\",\"population\":2078.932},{\"name\":\"Spain\",\"population\":46754.783},{\"name\":\"Austria\",\"population\":9006.4},{\"name\":\"Belgium\",\"population\":11589.616},{\"name\":\"France\",\"population\":65273.512},{\"name\":\"Germany\",\"population\":83783.945},{\"name\":\"Liechtenstein\",\"population\":38.137},{\"name\":\"Luxembourg\",\"population\":625.976},{\"name\":\"Monaco\",\"population\":39.244},{\"name\":\"Netherlands\",\"population\":17134.873},{\"name\":\"Switzerland\",\"population\":8654.618},{\"name\":\"Bermuda\",\"population\":62.273},{\"name\":\"Canada\",\"population\":37742.157},{\"name\":\"Greenland\",\"population\":56.772},{\"name\":\"Saint Pierre and Miquelon\",\"population\":5.795},{\"name\":\"US\",\"population\":331002.647}]","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"payload~> | $ | (\t    $cur_country := $.country_region;\t    $population := $$.countries[name = $cur_country  ].population;\t    {\t     \"population_in_million\" : $population/1000~>$round(2),\t     \"new_cases_per_million\" : new_cases_current*1000/$population ~>$round(2),\t     \"total_cases_per_million\" : total_conf_cases*1000/$population ~>$round(0)\t     } ) |\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":320,"wires":[["8637a5d1.94f4c8","b58129cd.f46a38"]]},{"id":"8637a5d1.94f4c8","type":"debug","z":"3739886a.1825b8","name":"population,... added","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":320,"wires":[]},{"id":"737a23b5.a584ec","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"passthru":false,"label":"retrieve","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":170,"y":20,"wires":[["48cc765f.3e0de8","bd53b32d.740eb"]]},{"id":"e3654952.086598","type":"ui_text","z":"3739886a.1825b8","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"name":"","label":"","format":"{{msg.payload}}","layout":"row-spread","x":890,"y":100,"wires":[]},{"id":"bd53b32d.740eb","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"retrieving data ...","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":20,"wires":[["e3654952.086598"]]},{"id":"4ccd263e.f616c8","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"date \" & last_date_retrieved & \" retrieved\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":440,"wires":[["e3654952.086598"]]},{"id":"4a582d99.bd4984","type":"ui_toast","z":"3739886a.1825b8","position":"dialog","displayTime":"20","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"","raw":true,"topic":"","name":"","x":490,"y":540,"wires":[[]]},{"id":"f721729f.198f","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":2,"width":0,"height":0,"passthru":false,"label":"info","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":80,"y":540,"wires":[["be40b73d.9caab8"]]},{"id":"be40b73d.9caab8","type":"template","z":"3739886a.1825b8","name":"","field":"payload","fieldType":"msg","format":"html","syntax":"plain","template":"<head>\n<style>\ntable {\n  font-family: arial, sans-serif;\n  border-collapse: collapse;\n  width: 100%;\n}\n\ntd, th {\n  border: 1px solid #dddddd;\n  text-align: left;\n  padding: 8px;\n}\n\ntr:nth-child(even) {\n  background-color: #dddddd;\n}\n</style>\n</head>\n<body>\n<table>\n    <tr>\n        <th>column</th>\n        <th>field</th>\n        <th>description</th>\n    </tr>\n    <tr>\n        <td>1</td>\n        <td>location</td>\n        <td>country / state</td>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>cases/day/million</td>\n        <td>average number of confirmed cases per day (over the last 7 days) per million persons.<br>\n            we have taken an average over the last 7 days to filter out any weekend effects.\n        </td>\n    </tr>\n    <tr>\n        <td>3</td>\n        <td>RN (Repr. Nr)</td>\n        <td>Reproduction number which is defined as <br><i>(conf_cases_last_week / conf_cases_week_before)</i><br>\n            where <i>conf_cases_last_week</i> is the total number of new confirmed cases registered last week.\n            A week is chosen to filter out any weekend effects.\n        </td>\n    </tr>\n    <tr>\n        <td>4</td>\n        <td>RN (-1 day)</td>\n        <td>same as previous column but then calculated for the previous day.  \n            So tomorrow this column shows the same value you can see today in previous column.\n        </td>\n    </tr>\n    <tr>\n        <td>5</td>\n        <td>RN (-1week)</td>\n        <td>same as 3rd column but then calculated for the previous week.  \n            So a week later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>6</td>\n        <td>RN (-2week)</td>\n        <td>same as 3rd column but then calculated for 2 weeks ago.  \n            So 2 weeks later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>7</td>\n        <td>RN (-3week)</td>\n        <td>same as 3rd column but then calculated for 3 weeks ago.  \n        </td>\n    </tr>\n    <tr>\n        <td>8</td>\n        <td>tot. cases/million</td>\n        <td>total confirmed cases (at date retrieved) per million.  So this includes all confirmed cases.\n        </td>\n    </tr>\n    <tr>\n        <td>9</td>\n        <td>popul. [million]</td>\n        <td>total country population expressed in million.\n        </td>\n    </tr>\n    <tr>\n        <td>10</td>\n        <td>tot. cases</td>\n        <td>total reported confirmed cases.\n        </td>\n    </tr>\n</table>\n</body>\n","output":"str","x":260,"y":540,"wires":[["4a582d99.bd4984"]]},{"id":"b58129cd.f46a38","type":"change","z":"3739886a.1825b8","name":"html with background color","rules":[{"t":"set","p":"payload","pt":"msg","to":"( /* 3 color gradient green (0,255,0) - yellow (255, 255, 0) - red (255,0,0)*/\t  $rep_nr_with_style_fn := function($rep_nr){ \t       \"<div style='background-color: rgb(\" &\t       ( $rep_nr <  0.6 ? \"0,255,0\"\t         : $rep_nr <= 1.0 ? (\t             $fraction255 := (($rep_nr - 0.6)*255/0.4)~>$round(0);\t             $fraction255 & \",\" & \t             ((255 - ( $fraction255*(255-255)/255))~>$round(0)) & \",0\";\t         )\t         : $rep_nr <= 2.0 ? (\t             $fraction255 := (($rep_nr - 1)*255)~>$round(0);\t             \"255,\" & (255 - $fraction255)  & \",0\";\t         )\t         : \"255,0,0\") & \t         \")'>\" & $rep_nr & \"</div>\"         \t  };\t  payload ~> | $ | {\t     \"html_reproduction_number\"          :  $rep_nr_with_style_fn(reproduction_number),\t     \"html_reproduction_number_1day_ago\" :  $rep_nr_with_style_fn(reproduction_number_1day_ago),\t     \"html_reproduction_number_1week_ago\" :  $rep_nr_with_style_fn(reproduction_number_1week_ago), \t     \"html_reproduction_number_2week_ago\" :  $rep_nr_with_style_fn(reproduction_number_2week_ago),\t     \"html_reproduction_number_3week_ago\" :  $rep_nr_with_style_fn(reproduction_number_3week_ago)\t  } |\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":380,"wires":[["e9092edc.7f8d3","4ccd263e.f616c8","94e3c9a.18b3d38"]]},{"id":"94e3c9a.18b3d38","type":"debug","z":"3739886a.1825b8","name":"with background color","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":380,"wires":[]},{"id":"984606ac.155a48","type":"ui_group","z":"","name":"table","tab":"78853c89.625cc4","order":2,"disp":false,"width":"28","collapse":false},{"id":"50d8c10e.e27b4","type":"ui_group","z":"","name":"commands","tab":"78853c89.625cc4","order":1,"disp":false,"width":"3","collapse":false},{"id":"78853c89.625cc4","type":"ui_tab","z":"","name":"COVID19 - country overview","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Note that unlike my other covid-19 related node-red flows this one is pretty light weight. So I would expect that you can deploy it without much trouble.

Here an example of the output it produced today:

If you press on the info button you will also get the documentation for the different columns:

I recommend to sort on the 2nd column "cases/day/million" in descending order.

As always ... any feedback, comments, remarks, suggestions are more than welcome.
Jan.

@janvda Jan, none of the RN sorts seem to work properly.

Hi Paul,

That is very true comment. It also proves that you have succeeded in running the flow :+1: :slight_smile:
The issue is that I am making use of node-red-node-ui-table and in order to set the background in the proper color I am feeding the cells with the actual html code. E.g.

<div style='background-color: rgb(255,36,0)'>1.86</div>`

So, I think that the node is no longer sorting on the value but on the actual html string I am providing as input.

I don't know if there is a way to fix this.

You can use a template node and create the table descriptions there. And take a look at the tabulator documentation

I had a closer look at it. It is not fully clear what you have in mind. I can remove the surrounding html tag that is setting the background color this will assure that sorting is working but then of course without any background color :crying_cat_face: .

I have found a workaround (hack) for the sorting on the Reproduction number columns. So now this is working.

I added a class tag (J00116 in the example below) with sole purpose to assure that alphabetically sorting is same as sorting on the value:

E.g. <div class='J00116' style='background-color: rgb(255,214,0)'>1.16</div>

UPDATE 2020-06-23 : updated flow below - it now contains the full flow instead of only the node that I changed.

[{"id":"3739886a.1825b8","type":"tab","label":"COVID19 - country comparison","disabled":false,"info":""},{"id":"52a875d9.1a5e1c","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"78853c89.625cc4","type":"ui_tab","z":"","name":"COVID19 - country overview","icon":"dashboard","order":1,"disabled":false,"hidden":false},{"id":"984606ac.155a48","type":"ui_group","z":"","name":"table","tab":"78853c89.625cc4","order":2,"disp":false,"width":"28","collapse":false},{"id":"50d8c10e.e27b4","type":"ui_group","z":"","name":"commands","tab":"78853c89.625cc4","order":1,"disp":false,"width":"3","collapse":false},{"id":"48cc765f.3e0de8","type":"http request","z":"3739886a.1825b8","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv","tls":"","persist":false,"proxy":"","authType":"","x":250,"y":80,"wires":[["d78d0310.f64ee","b1fdd2fb.c9e67"]]},{"id":"d78d0310.f64ee","type":"debug","z":"3739886a.1825b8","name":"http response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":580,"y":80,"wires":[]},{"id":"b1fdd2fb.c9e67","type":"csv","z":"3739886a.1825b8","name":"","sep":",","hdrin":false,"hdrout":true,"multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"x":230,"y":140,"wires":[["79857f5e.29c53","d78c600d.35a92"]]},{"id":"79857f5e.29c53","type":"debug","z":"3739886a.1825b8","name":"csv2array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":560,"y":140,"wires":[]},{"id":"d78c600d.35a92","type":"change","z":"3739886a.1825b8","name":"calculate reproduction rates,...","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* payload[0]~>$sift(function($v,$k){ $k~>/col/}) */\t(\t   $header := payload[0]~>$each(function($v,$k) { { \"key\" : $k, \"val\" : $v} } );\t   {\t     \"header\" : $header,\t     \"last_date_retrieved\" : $header[-1].val,\t     \"last_col_min_8\" : $header[-8],\t     \"location\" : payload[col2 != \"Country/Region\"].(\t        $new_cases_current   := ($lookup($,$header[-1].key)-$lookup($,$header[-8].key))/7;\t        $new_cases_1day_ago  := ($lookup($,$header[-2].key)-$lookup($,$header[-9].key))/7; \t        $new_cases_8days_ago  := ($lookup($,$header[-9].key)-$lookup($,$header[-16].key))/7;     \t        $new_cases_1week_ago := ($lookup($,$header[-8].key)-$lookup($,$header[-15].key))/7;\t        $new_cases_2week_ago := ($lookup($,$header[-15].key)-$lookup($,$header[-22].key))/7;\t        $new_cases_3week_ago := ($lookup($,$header[-22].key)-$lookup($,$header[-29].key))/7;    \t        $new_cases_4week_ago := ($lookup($,$header[-29].key)-$lookup($,$header[-36].key))/7; \t        {\t         \"province_state\" : col1,\t         \"country_region\" : col2,\t         \"latitude\"       : col3,\t         \"longitude\"      : col4,\t         \"distance\"       : -1,\t         \"total_conf_cases\": $lookup($,$header[-1].key),\t         \"new_cases_current\" : $new_cases_current,\t         \"new_cases_1week_ago\" : $new_cases_1week_ago,\t         \"reproduction_number\" :   $new_cases_1week_ago = 0 \t                                   ? ($new_cases_current = 0 ? 0 : 9999)\t                                   : ($new_cases_current/$new_cases_1week_ago)~>$round(2),\t         \"reproduction_number_1day_ago\" : $new_cases_8days_ago = 0 \t                                          ? ($new_cases_8days_ago = 0 ? 0 : 9999)\t                                          : ($new_cases_1day_ago/$new_cases_8days_ago)~>$round(2),\t         \"reproduction_number_1week_ago\" :   $new_cases_2week_ago = 0 \t                                             ? ($new_cases_1week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_1week_ago/$new_cases_2week_ago)~>$round(2),\t         \"reproduction_number_2week_ago\" :   $new_cases_3week_ago = 0 \t                                             ? ($new_cases_2week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_2week_ago/$new_cases_3week_ago)~>$round(2),  \t         \"reproduction_number_3week_ago\" :   $new_cases_4week_ago = 0 \t                                             ? ($new_cases_3week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_3week_ago/$new_cases_4week_ago)~>$round(2)\t        }\t      )\t   }\t   \t)","tot":"jsonata"},{"t":"set","p":"last_date_retrieved","pt":"msg","to":"payload.last_date_retrieved","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":200,"wires":[["9124a122.24074","4e81e87d.b3abf8"]]},{"id":"9124a122.24074","type":"debug","z":"3739886a.1825b8","name":"with reproduction rates,...","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":610,"y":200,"wires":[]},{"id":"4e81e87d.b3abf8","type":"change","z":"3739886a.1825b8","name":"select only countries","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.location[$not($exists(province_state))]","tot":"jsonata"},{"t":"set","p":"options","pt":"msg","to":"payload.country_region","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":260,"wires":[["4406d2a6.88b6ac","7f27f9ea.a5ea88"]]},{"id":"4406d2a6.88b6ac","type":"debug","z":"3739886a.1825b8","name":"only countries","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":580,"y":260,"wires":[]},{"id":"e9092edc.7f8d3","type":"ui_table","z":"3739886a.1825b8","group":"984606ac.155a48","name":"","order":0,"width":"28","height":"15","columns":[{"field":"country_region","title":"location","width":"16px","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"new_cases_per_million","title":"cases/day/milion","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number","title":"RN (Repr. Nr)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1day_ago","title":"RN (-1 day)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1week_ago","title":"RN  (-1week)","width":"7px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_2week_ago","title":"RN (-2week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_3week_ago","title":"RN (-3week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"total_cases_per_million","title":"tot. cases/million","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"population_in_million","title":"popul. [million]","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"total_conf_cases","title":"tot.  cases","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":230,"y":460,"wires":[]},{"id":"7f27f9ea.a5ea88","type":"change","z":"3739886a.1825b8","name":"add population, ...","rules":[{"t":"set","p":"countries","pt":"msg","to":"[{\"name\":\"Burundi\",\"population\":11890.781},{\"name\":\"Comoros\",\"population\":869.595},{\"name\":\"Djibouti\",\"population\":988.002},{\"name\":\"Eritrea\",\"population\":3546.427},{\"name\":\"Ethiopia\",\"population\":114963.583},{\"name\":\"Kenya\",\"population\":53771.3},{\"name\":\"Madagascar\",\"population\":27691.019},{\"name\":\"Malawi\",\"population\":19129.955},{\"name\":\"Mauritius\",\"population\":1271.767},{\"name\":\"Mayotte\",\"population\":272.813},{\"name\":\"Mozambique\",\"population\":31255.435},{\"name\":\"Réunion\",\"population\":895.308},{\"name\":\"Rwanda\",\"population\":12952.209},{\"name\":\"Seychelles\",\"population\":98.34},{\"name\":\"Somalia\",\"population\":15893.219},{\"name\":\"South Sudan\",\"population\":11193.729},{\"name\":\"Uganda\",\"population\":45741},{\"name\":\"Tanzania\",\"population\":59734.213},{\"name\":\"Zambia\",\"population\":18383.956},{\"name\":\"Zimbabwe\",\"population\":14862.927},{\"name\":\"Angola\",\"population\":32866.268},{\"name\":\"Cameroon\",\"population\":26545.864},{\"name\":\"Central African Republic\",\"population\":4829.764},{\"name\":\"Chad\",\"population\":16425.859},{\"name\":\"Congo (Brazzaville)\",\"population\":5518.092},{\"name\":\"Congo (Kinshasa)\",\"population\":89561.404},{\"name\":\"Equatorial Guinea\",\"population\":1402.985},{\"name\":\"Gabon\",\"population\":2225.728},{\"name\":\"Sao Tome and Principe\",\"population\":219.161},{\"name\":\"Botswana\",\"population\":2351.625},{\"name\":\"Eswatini\",\"population\":1160.164},{\"name\":\"Lesotho\",\"population\":2142.252},{\"name\":\"Namibia\",\"population\":2540.916},{\"name\":\"South Africa\",\"population\":59308.69},{\"name\":\"Benin\",\"population\":12123.198},{\"name\":\"Burkina Faso\",\"population\":20903.278},{\"name\":\"Cabo Verde\",\"population\":555.988},{\"name\":\"Cote d'Ivoire\",\"population\":26378.275},{\"name\":\"Gambia\",\"population\":2416.664},{\"name\":\"Ghana\",\"population\":31072.945},{\"name\":\"Guinea\",\"population\":13132.792},{\"name\":\"Guinea-Bissau\",\"population\":1967.998},{\"name\":\"Liberia\",\"population\":5057.677},{\"name\":\"Mali\",\"population\":20250.834},{\"name\":\"Mauritania\",\"population\":4649.66},{\"name\":\"Niger\",\"population\":24206.636},{\"name\":\"Nigeria\",\"population\":206139.587},{\"name\":\"Saint Helena\",\"population\":6.071},{\"name\":\"Senegal\",\"population\":16743.93},{\"name\":\"Sierra Leone\",\"population\":7976.985},{\"name\":\"Togo\",\"population\":8278.737},{\"name\":\"Algeria\",\"population\":43851.043},{\"name\":\"Egypt\",\"population\":102334.403},{\"name\":\"Libya\",\"population\":6871.287},{\"name\":\"Morocco\",\"population\":36910.558},{\"name\":\"Sudan\",\"population\":43849.269},{\"name\":\"Tunisia\",\"population\":11818.618},{\"name\":\"Western Sahara\",\"population\":597.33},{\"name\":\"Armenia\",\"population\":2963.234},{\"name\":\"Azerbaijan\",\"population\":10139.175},{\"name\":\"Bahrain\",\"population\":1701.583},{\"name\":\"Cyprus\",\"population\":1207.361},{\"name\":\"Georgia\",\"population\":3989.175},{\"name\":\"Iraq\",\"population\":40222.503},{\"name\":\"Israel\",\"population\":8655.541},{\"name\":\"Jordan\",\"population\":10203.14},{\"name\":\"Kuwait\",\"population\":4270.563},{\"name\":\"Lebanon\",\"population\":6825.442},{\"name\":\"Oman\",\"population\":5106.622},{\"name\":\"Qatar\",\"population\":2881.06},{\"name\":\"Saudi Arabia\",\"population\":34813.867},{\"name\":\"State of Palestine\",\"population\":5101.416},{\"name\":\"Syria\",\"population\":17500.657},{\"name\":\"Turkey\",\"population\":84339.067},{\"name\":\"United Arab Emirates\",\"population\":9890.4},{\"name\":\"Yemen\",\"population\":29825.968},{\"name\":\"Kazakhstan\",\"population\":18776.707},{\"name\":\"Kyrgyzstan\",\"population\":6524.191},{\"name\":\"Tajikistan\",\"population\":9537.642},{\"name\":\"Turkmenistan\",\"population\":6031.187},{\"name\":\"Uzbekistan\",\"population\":33469.199},{\"name\":\"Afghanistan\",\"population\":38928.341},{\"name\":\"Bangladesh\",\"population\":164689.383},{\"name\":\"Bhutan\",\"population\":771.612},{\"name\":\"India\",\"population\":1380004.385},{\"name\":\"Iran\",\"population\":83992.953},{\"name\":\"Maldives\",\"population\":540.542},{\"name\":\"Nepal\",\"population\":29136.808},{\"name\":\"Pakistan\",\"population\":220892.331},{\"name\":\"Sri Lanka\",\"population\":21413.25},{\"name\":\"China\",\"population\":1439323.774},{\"name\":\"China, Hong Kong SAR\",\"population\":7496.988},{\"name\":\"China, Macao SAR\",\"population\":649.342},{\"name\":\"Taiwan*\",\"population\":23816.775},{\"name\":\"Dem. People's Republic of Korea\",\"population\":25778.815},{\"name\":\"Japan\",\"population\":126476.458},{\"name\":\"Mongolia\",\"population\":3278.292},{\"name\":\"Korea, South\",\"population\":51269.183},{\"name\":\"Brunei\",\"population\":437.483},{\"name\":\"Cambodia\",\"population\":16718.971},{\"name\":\"Indonesia\",\"population\":273523.621},{\"name\":\"Laos\",\"population\":7275.556},{\"name\":\"Malaysia\",\"population\":32365.998},{\"name\":\"Myanmar\",\"population\":54409.794},{\"name\":\"Philippines\",\"population\":109581.085},{\"name\":\"Singapore\",\"population\":5850.343},{\"name\":\"Thailand\",\"population\":69799.978},{\"name\":\"Timor-Leste\",\"population\":1318.442},{\"name\":\"Vietnam\",\"population\":97338.583},{\"name\":\"Anguilla\",\"population\":15.002},{\"name\":\"Antigua and Barbuda\",\"population\":97.928},{\"name\":\"Aruba\",\"population\":106.766},{\"name\":\"Bahamas\",\"population\":393.248},{\"name\":\"Barbados\",\"population\":287.371},{\"name\":\"Bonaire, Sint Eustatius and Saba\",\"population\":26.221},{\"name\":\"British Virgin Islands\",\"population\":30.237},{\"name\":\"Cayman Islands\",\"population\":65.72},{\"name\":\"Cuba\",\"population\":11326.616},{\"name\":\"Curaçao\",\"population\":164.1},{\"name\":\"Dominica\",\"population\":71.991},{\"name\":\"Dominican Republic\",\"population\":10847.904},{\"name\":\"Grenada\",\"population\":112.519},{\"name\":\"Guadeloupe\",\"population\":400.127},{\"name\":\"Haiti\",\"population\":11402.533},{\"name\":\"Jamaica\",\"population\":2961.161},{\"name\":\"Martinique\",\"population\":375.265},{\"name\":\"Montserrat\",\"population\":4.999},{\"name\":\"Puerto Rico\",\"population\":2860.84},{\"name\":\"Saint Barthélem\",\"population\":9.885},{\"name\":\"Saint Kitts and Nevis\",\"population\":53.192},{\"name\":\"Saint Lucia\",\"population\":183.629},{\"name\":\"Saint Martin (French part)\",\"population\":38.659},{\"name\":\"Saint Vincent and the Grenadines\",\"population\":110.947},{\"name\":\"Sint Maarten (Dutch part)\",\"population\":42.882},{\"name\":\"Trinidad and Tobago\",\"population\":1399.491},{\"name\":\"Turks and Caicos Islands\",\"population\":38.718},{\"name\":\"United States Virgin Islands\",\"population\":104.423},{\"name\":\"Belize\",\"population\":397.621},{\"name\":\"Costa Rica\",\"population\":5094.114},{\"name\":\"El Salvador\",\"population\":6486.201},{\"name\":\"Guatemala\",\"population\":17915.567},{\"name\":\"Honduras\",\"population\":9904.608},{\"name\":\"Mexico\",\"population\":128932.753},{\"name\":\"Nicaragua\",\"population\":6624.554},{\"name\":\"Panama\",\"population\":4314.768},{\"name\":\"Argentina\",\"population\":45195.777},{\"name\":\"Bolivia\",\"population\":11673.029},{\"name\":\"Brazil\",\"population\":212559.409},{\"name\":\"Chile\",\"population\":19116.209},{\"name\":\"Colombia\",\"population\":50882.884},{\"name\":\"Ecuador\",\"population\":17643.06},{\"name\":\"Falkland Islands (Malvinas)\",\"population\":3.483},{\"name\":\"French Guiana\",\"population\":298.682},{\"name\":\"Guyana\",\"population\":786.559},{\"name\":\"Paraguay\",\"population\":7132.53},{\"name\":\"Peru\",\"population\":32971.846},{\"name\":\"Suriname\",\"population\":586.634},{\"name\":\"Uruguay\",\"population\":3473.727},{\"name\":\"Venezuela\",\"population\":28435.943},{\"name\":\"Australia\",\"population\":25499.881},{\"name\":\"New Zealand\",\"population\":4822.233},{\"name\":\"Fiji\",\"population\":896.444},{\"name\":\"New Caledonia\",\"population\":285.491},{\"name\":\"Papua New Guinea\",\"population\":8947.027},{\"name\":\"Solomon Islands\",\"population\":686.878},{\"name\":\"Vanuatu\",\"population\":307.15},{\"name\":\"Guam\",\"population\":168.783},{\"name\":\"Kiribati\",\"population\":119.446},{\"name\":\"Marshall Islands\",\"population\":59.194},{\"name\":\"Micronesia (Fed. States of)\",\"population\":115.021},{\"name\":\"Nauru\",\"population\":10.834},{\"name\":\"Northern Mariana Islands\",\"population\":57.557},{\"name\":\"Palau\",\"population\":18.092},{\"name\":\"American Samoa\",\"population\":55.197},{\"name\":\"Cook Islands\",\"population\":17.564},{\"name\":\"French Polynesia\",\"population\":280.904},{\"name\":\"Niue\",\"population\":1.618},{\"name\":\"Samoa\",\"population\":198.41},{\"name\":\"Tokelau\",\"population\":1.35},{\"name\":\"Tonga\",\"population\":105.697},{\"name\":\"Tuvalu\",\"population\":11.792},{\"name\":\"Wallis and Futuna Islands\",\"population\":11.246},{\"name\":\"Belarus\",\"population\":9449.321},{\"name\":\"Bulgaria\",\"population\":6948.445},{\"name\":\"Czechia\",\"population\":10708.982},{\"name\":\"Hungary\",\"population\":9660.35},{\"name\":\"Poland\",\"population\":37846.605},{\"name\":\"Moldova\",\"population\":4033.963},{\"name\":\"Romania\",\"population\":19237.682},{\"name\":\"Russia\",\"population\":145934.46},{\"name\":\"Slovakia\",\"population\":5459.643},{\"name\":\"Ukraine\",\"population\":43733.759},{\"name\":\"Channel Islands\",\"population\":173.859},{\"name\":\"Denmark\",\"population\":5792.203},{\"name\":\"Estonia\",\"population\":1326.539},{\"name\":\"Faroe Islands\",\"population\":48.865},{\"name\":\"Finland\",\"population\":5540.718},{\"name\":\"Iceland\",\"population\":341.25},{\"name\":\"Ireland\",\"population\":4937.796},{\"name\":\"Isle of Man\",\"population\":85.032},{\"name\":\"Latvia\",\"population\":1886.202},{\"name\":\"Lithuania\",\"population\":2722.291},{\"name\":\"Norway\",\"population\":5421.242},{\"name\":\"Sweden\",\"population\":10099.27},{\"name\":\"United Kingdom\",\"population\":67886.004},{\"name\":\"Albania\",\"population\":2877.8},{\"name\":\"Andorra\",\"population\":77.265},{\"name\":\"Bosnia and Herzegovina\",\"population\":3280.815},{\"name\":\"Croatia\",\"population\":4105.268},{\"name\":\"Gibraltar\",\"population\":33.691},{\"name\":\"Greece\",\"population\":10423.056},{\"name\":\"Holy See\",\"population\":0.809},{\"name\":\"Italy\",\"population\":60461.828},{\"name\":\"Malta\",\"population\":441.539},{\"name\":\"Montenegro\",\"population\":628.062},{\"name\":\"North Macedonia\",\"population\":2083.38},{\"name\":\"Portugal\",\"population\":10196.707},{\"name\":\"San Marino\",\"population\":33.938},{\"name\":\"Serbia\",\"population\":8737.37},{\"name\":\"Slovenia\",\"population\":2078.932},{\"name\":\"Spain\",\"population\":46754.783},{\"name\":\"Austria\",\"population\":9006.4},{\"name\":\"Belgium\",\"population\":11589.616},{\"name\":\"France\",\"population\":65273.512},{\"name\":\"Germany\",\"population\":83783.945},{\"name\":\"Liechtenstein\",\"population\":38.137},{\"name\":\"Luxembourg\",\"population\":625.976},{\"name\":\"Monaco\",\"population\":39.244},{\"name\":\"Netherlands\",\"population\":17134.873},{\"name\":\"Switzerland\",\"population\":8654.618},{\"name\":\"Bermuda\",\"population\":62.273},{\"name\":\"Canada\",\"population\":37742.157},{\"name\":\"Greenland\",\"population\":56.772},{\"name\":\"Saint Pierre and Miquelon\",\"population\":5.795},{\"name\":\"US\",\"population\":331002.647}]","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"payload~> | $ | (\t    $cur_country := $.country_region;\t    $population := $$.countries[name = $cur_country  ].population;\t    {\t     \"population_in_million\" : $population/1000~>$round(2),\t     \"new_cases_per_million\" : new_cases_current*1000/$population ~>$round(2),\t     \"total_cases_per_million\" : total_conf_cases*1000/$population ~>$round(0)\t     } ) |\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":340,"wires":[["8637a5d1.94f4c8","b58129cd.f46a38"]]},{"id":"8637a5d1.94f4c8","type":"debug","z":"3739886a.1825b8","name":"population,... added","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":340,"wires":[]},{"id":"737a23b5.a584ec","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"passthru":false,"label":"retrieve","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":170,"y":20,"wires":[["48cc765f.3e0de8","bd53b32d.740eb"]]},{"id":"e3654952.086598","type":"ui_text","z":"3739886a.1825b8","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"name":"","label":"","format":"{{msg.payload}}","layout":"row-spread","x":950,"y":100,"wires":[]},{"id":"bd53b32d.740eb","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"retrieving data ...","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":20,"wires":[["e3654952.086598"]]},{"id":"4ccd263e.f616c8","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"date \" & last_date_retrieved & \" retrieved\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":460,"wires":[["e3654952.086598"]]},{"id":"4a582d99.bd4984","type":"ui_toast","z":"3739886a.1825b8","position":"dialog","displayTime":"20","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"","raw":true,"topic":"","name":"","x":490,"y":520,"wires":[[]]},{"id":"f721729f.198f","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":2,"width":0,"height":0,"passthru":false,"label":"info","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":80,"y":520,"wires":[["be40b73d.9caab8"]]},{"id":"be40b73d.9caab8","type":"template","z":"3739886a.1825b8","name":"","field":"payload","fieldType":"msg","format":"html","syntax":"plain","template":"<head>\n<style>\ntable {\n  font-family: arial, sans-serif;\n  border-collapse: collapse;\n  width: 100%;\n}\n\ntd, th {\n  border: 1px solid #dddddd;\n  text-align: left;\n  padding: 8px;\n}\n\ntr:nth-child(even) {\n  background-color: #dddddd;\n}\n</style>\n</head>\n<body>\n<table>\n    <tr>\n        <th>column</th>\n        <th>field</th>\n        <th>description</th>\n    </tr>\n    <tr>\n        <td>1</td>\n        <td>location</td>\n        <td>country / state</td>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>cases/day/million</td>\n        <td>average number of confirmed cases per day (over the last 7 days) per million persons.<br>\n            we have taken an average over the last 7 days to filter out any weekend effects.\n        </td>\n    </tr>\n    <tr>\n        <td>3</td>\n        <td>RN (Repr. Nr)</td>\n        <td>Reproduction number which is defined as <br><i>(conf_cases_last_week / conf_cases_week_before)</i><br>\n            where <i>conf_cases_last_week</i> is the total number of new confirmed cases registered last week.\n            A week is chosen to filter out any weekend effects.\n        </td>\n    </tr>\n    <tr>\n        <td>4</td>\n        <td>RN (-1 day)</td>\n        <td>same as previous column but then calculated for the previous day.  \n            So tomorrow this column shows the same value you can see today in previous column.\n        </td>\n    </tr>\n    <tr>\n        <td>5</td>\n        <td>RN (-1week)</td>\n        <td>same as 3rd column but then calculated for the previous week.  \n            So a week later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>6</td>\n        <td>RN (-2week)</td>\n        <td>same as 3rd column but then calculated for 2 weeks ago.  \n            So 2 weeks later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>7</td>\n        <td>RN (-3week)</td>\n        <td>same as 3rd column but then calculated for 3 weeks ago.  \n        </td>\n    </tr>\n    <tr>\n        <td>8</td>\n        <td>tot. cases/million</td>\n        <td>total confirmed cases (at date retrieved) per million.  So this includes all confirmed cases.\n        </td>\n    </tr>\n    <tr>\n        <td>9</td>\n        <td>popul. [million]</td>\n        <td>total country population expressed in million.\n        </td>\n    </tr>\n    <tr>\n        <td>10</td>\n        <td>tot. cases</td>\n        <td>total reported confirmed cases.\n        </td>\n    </tr>\n</table>\n</body>\n","output":"str","x":260,"y":520,"wires":[["4a582d99.bd4984"]]},{"id":"b58129cd.f46a38","type":"change","z":"3739886a.1825b8","name":"html with background color","rules":[{"t":"set","p":"payload","pt":"msg","to":"( /* 3 color gradient green (0,255,0) - yellow (255, 255, 0) - red (255,0,0) \t     note that class tag is added as a hack to assure that sorting is workign properly\t   */\t  $rep_nr_with_style_fn := function($rep_nr){ \t       \"<div class='J\" & $substring($string($rep_nr/1000),2) &\t       \"' style='background-color: rgb(\" &\t       ( $rep_nr <  0.6 ? \"0,255,0\"\t         : $rep_nr <= 1.0 ? (\t             $fraction255 := (($rep_nr - 0.6)*255/0.4)~>$round(0);\t             $fraction255 & \",\" & \t             ((255 - ( $fraction255*(255-255)/255))~>$round(0)) & \",0\";\t         )\t         : $rep_nr <= 2.0 ? (\t             $fraction255 := (($rep_nr - 1)*255)~>$round(0);\t             \"255,\" & (255 - $fraction255)  & \",0\";\t         )\t         : \"255,0,0\") & \t         \")'>\" & $rep_nr & \"</div>\"         \t  };\t  payload ~> | $ | {\t     \"html_reproduction_number\"          :  $rep_nr_with_style_fn(reproduction_number),\t     \"html_reproduction_number_1day_ago\" :  $rep_nr_with_style_fn(reproduction_number_1day_ago),\t     \"html_reproduction_number_1week_ago\" :  $rep_nr_with_style_fn(reproduction_number_1week_ago), \t     \"html_reproduction_number_2week_ago\" :  $rep_nr_with_style_fn(reproduction_number_2week_ago),\t     \"html_reproduction_number_3week_ago\" :  $rep_nr_with_style_fn(reproduction_number_3week_ago)\t  } |\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":400,"wires":[["e9092edc.7f8d3","4ccd263e.f616c8","94e3c9a.18b3d38"]]},{"id":"94e3c9a.18b3d38","type":"debug","z":"3739886a.1825b8","name":"with background color","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":400,"wires":[]}]

Nicely done! - note all that is in the last flow is the one change node not the full flow. I just popped it into the flow replacing the other change node.

I'm suprised that there isn't a deaths column.

Stupid me - I updated the post with the full flow - thanks for the comment.

That is indeed a possible extension. For the moment I am focusing on the confirmed cases as they are a better indicator of the current status (deaths are following confirmed cases with a delay of weeks).

1 Like

NEW FEATURE: "Distance Column added"

So it is now possible to select a country, and the flow will then calculate the distance for all countries to the selected country based on the latitude/longitude formulas described at https://www.movable-type.co.uk/scripts/latlong.html
This makes it possible to sort the list based on proximity to a selected country.

The flow:

[{"id":"3739886a.1825b8","type":"tab","label":"COVID19 - country comparison","disabled":false,"info":""},{"id":"52a875d9.1a5e1c","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"78853c89.625cc4","type":"ui_tab","z":"","name":"COVID19 - country overview","icon":"dashboard","order":1,"disabled":false,"hidden":false},{"id":"984606ac.155a48","type":"ui_group","z":"","name":"table","tab":"78853c89.625cc4","order":2,"disp":false,"width":"28","collapse":false},{"id":"50d8c10e.e27b4","type":"ui_group","z":"","name":"commands","tab":"78853c89.625cc4","order":1,"disp":false,"width":"3","collapse":false},{"id":"48cc765f.3e0de8","type":"http request","z":"3739886a.1825b8","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv","tls":"","persist":false,"proxy":"","authType":"","x":250,"y":80,"wires":[["d78d0310.f64ee","b1fdd2fb.c9e67"]]},{"id":"d78d0310.f64ee","type":"debug","z":"3739886a.1825b8","name":"http response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":580,"y":80,"wires":[]},{"id":"b1fdd2fb.c9e67","type":"csv","z":"3739886a.1825b8","name":"","sep":",","hdrin":false,"hdrout":true,"multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"x":230,"y":140,"wires":[["79857f5e.29c53","d78c600d.35a92"]]},{"id":"79857f5e.29c53","type":"debug","z":"3739886a.1825b8","name":"csv2array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":560,"y":140,"wires":[]},{"id":"d78c600d.35a92","type":"change","z":"3739886a.1825b8","name":"calculate reproduction rates,...","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* payload[0]~>$sift(function($v,$k){ $k~>/col/}) */\t(\t   $header := payload[0]~>$each(function($v,$k) { { \"key\" : $k, \"val\" : $v} } );\t   {\t     \"header\" : $header,\t     \"last_date_retrieved\" : $header[-1].val,\t     \"last_col_min_8\" : $header[-8],\t     \"location\" : payload[col2 != \"Country/Region\"].(\t        $new_cases_current   := ($lookup($,$header[-1].key)-$lookup($,$header[-8].key))/7;\t        $new_cases_1day_ago  := ($lookup($,$header[-2].key)-$lookup($,$header[-9].key))/7; \t        $new_cases_8days_ago  := ($lookup($,$header[-9].key)-$lookup($,$header[-16].key))/7;     \t        $new_cases_1week_ago := ($lookup($,$header[-8].key)-$lookup($,$header[-15].key))/7;\t        $new_cases_2week_ago := ($lookup($,$header[-15].key)-$lookup($,$header[-22].key))/7;\t        $new_cases_3week_ago := ($lookup($,$header[-22].key)-$lookup($,$header[-29].key))/7;    \t        $new_cases_4week_ago := ($lookup($,$header[-29].key)-$lookup($,$header[-36].key))/7; \t        {\t         \"province_state\" : col1,\t         \"country_region\" : col2,\t         \"latitude\"       : col3,\t         \"longitude\"      : col4,\t         \"distance\"       : -1,\t         \"total_conf_cases\": $lookup($,$header[-1].key),\t         \"new_cases_current\" : $new_cases_current,\t         \"new_cases_1week_ago\" : $new_cases_1week_ago,\t         \"reproduction_number\" :   $new_cases_1week_ago = 0 \t                                   ? ($new_cases_current = 0 ? 0 : 9999)\t                                   : ($new_cases_current/$new_cases_1week_ago)~>$round(2),\t         \"reproduction_number_1day_ago\" : $new_cases_8days_ago = 0 \t                                          ? ($new_cases_8days_ago = 0 ? 0 : 9999)\t                                          : ($new_cases_1day_ago/$new_cases_8days_ago)~>$round(2),\t         \"reproduction_number_1week_ago\" :   $new_cases_2week_ago = 0 \t                                             ? ($new_cases_1week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_1week_ago/$new_cases_2week_ago)~>$round(2),\t         \"reproduction_number_2week_ago\" :   $new_cases_3week_ago = 0 \t                                             ? ($new_cases_2week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_2week_ago/$new_cases_3week_ago)~>$round(2),  \t         \"reproduction_number_3week_ago\" :   $new_cases_4week_ago = 0 \t                                             ? ($new_cases_3week_ago = 0 ? 0 : 9999)\t                                             : ($new_cases_3week_ago/$new_cases_4week_ago)~>$round(2)\t        }\t      )\t   }\t   \t)","tot":"jsonata"},{"t":"set","p":"last_date_retrieved","pt":"msg","to":"payload.last_date_retrieved","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":200,"wires":[["9124a122.24074","4e81e87d.b3abf8"]]},{"id":"9124a122.24074","type":"debug","z":"3739886a.1825b8","name":"with reproduction rates,...","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":610,"y":200,"wires":[]},{"id":"4e81e87d.b3abf8","type":"change","z":"3739886a.1825b8","name":"select only countries","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.location[$not($exists(province_state))]","tot":"jsonata"},{"t":"set","p":"options","pt":"msg","to":"payload.country_region","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":260,"wires":[["4406d2a6.88b6ac","7f27f9ea.a5ea88","631625f5.c0ae7c"]]},{"id":"4406d2a6.88b6ac","type":"debug","z":"3739886a.1825b8","name":"only countries","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":580,"y":240,"wires":[]},{"id":"e9092edc.7f8d3","type":"ui_table","z":"3739886a.1825b8","group":"984606ac.155a48","name":"","order":0,"width":"28","height":"15","columns":[{"field":"country_region","title":"location","width":"14px","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"distance","title":"distance","width":"5px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"new_cases_per_million","title":"cases/day/milion","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number","title":"RN (Repr. Nr)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1day_ago","title":"RN (-1 day)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1week_ago","title":"RN  (-1week)","width":"7px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_2week_ago","title":"RN (-2week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_3week_ago","title":"RN (-3week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"total_cases_per_million","title":"tot. cases/million","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"population_in_million","title":"popul. [million]","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"total_conf_cases","title":"tot.  cases","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":750,"y":620,"wires":[]},{"id":"7f27f9ea.a5ea88","type":"change","z":"3739886a.1825b8","name":"add population, ...","rules":[{"t":"set","p":"countries","pt":"msg","to":"[{\"name\":\"Burundi\",\"population\":11890.781},{\"name\":\"Comoros\",\"population\":869.595},{\"name\":\"Djibouti\",\"population\":988.002},{\"name\":\"Eritrea\",\"population\":3546.427},{\"name\":\"Ethiopia\",\"population\":114963.583},{\"name\":\"Kenya\",\"population\":53771.3},{\"name\":\"Madagascar\",\"population\":27691.019},{\"name\":\"Malawi\",\"population\":19129.955},{\"name\":\"Mauritius\",\"population\":1271.767},{\"name\":\"Mayotte\",\"population\":272.813},{\"name\":\"Mozambique\",\"population\":31255.435},{\"name\":\"Réunion\",\"population\":895.308},{\"name\":\"Rwanda\",\"population\":12952.209},{\"name\":\"Seychelles\",\"population\":98.34},{\"name\":\"Somalia\",\"population\":15893.219},{\"name\":\"South Sudan\",\"population\":11193.729},{\"name\":\"Uganda\",\"population\":45741},{\"name\":\"Tanzania\",\"population\":59734.213},{\"name\":\"Zambia\",\"population\":18383.956},{\"name\":\"Zimbabwe\",\"population\":14862.927},{\"name\":\"Angola\",\"population\":32866.268},{\"name\":\"Cameroon\",\"population\":26545.864},{\"name\":\"Central African Republic\",\"population\":4829.764},{\"name\":\"Chad\",\"population\":16425.859},{\"name\":\"Congo (Brazzaville)\",\"population\":5518.092},{\"name\":\"Congo (Kinshasa)\",\"population\":89561.404},{\"name\":\"Equatorial Guinea\",\"population\":1402.985},{\"name\":\"Gabon\",\"population\":2225.728},{\"name\":\"Sao Tome and Principe\",\"population\":219.161},{\"name\":\"Botswana\",\"population\":2351.625},{\"name\":\"Eswatini\",\"population\":1160.164},{\"name\":\"Lesotho\",\"population\":2142.252},{\"name\":\"Namibia\",\"population\":2540.916},{\"name\":\"South Africa\",\"population\":59308.69},{\"name\":\"Benin\",\"population\":12123.198},{\"name\":\"Burkina Faso\",\"population\":20903.278},{\"name\":\"Cabo Verde\",\"population\":555.988},{\"name\":\"Cote d'Ivoire\",\"population\":26378.275},{\"name\":\"Gambia\",\"population\":2416.664},{\"name\":\"Ghana\",\"population\":31072.945},{\"name\":\"Guinea\",\"population\":13132.792},{\"name\":\"Guinea-Bissau\",\"population\":1967.998},{\"name\":\"Liberia\",\"population\":5057.677},{\"name\":\"Mali\",\"population\":20250.834},{\"name\":\"Mauritania\",\"population\":4649.66},{\"name\":\"Niger\",\"population\":24206.636},{\"name\":\"Nigeria\",\"population\":206139.587},{\"name\":\"Saint Helena\",\"population\":6.071},{\"name\":\"Senegal\",\"population\":16743.93},{\"name\":\"Sierra Leone\",\"population\":7976.985},{\"name\":\"Togo\",\"population\":8278.737},{\"name\":\"Algeria\",\"population\":43851.043},{\"name\":\"Egypt\",\"population\":102334.403},{\"name\":\"Libya\",\"population\":6871.287},{\"name\":\"Morocco\",\"population\":36910.558},{\"name\":\"Sudan\",\"population\":43849.269},{\"name\":\"Tunisia\",\"population\":11818.618},{\"name\":\"Western Sahara\",\"population\":597.33},{\"name\":\"Armenia\",\"population\":2963.234},{\"name\":\"Azerbaijan\",\"population\":10139.175},{\"name\":\"Bahrain\",\"population\":1701.583},{\"name\":\"Cyprus\",\"population\":1207.361},{\"name\":\"Georgia\",\"population\":3989.175},{\"name\":\"Iraq\",\"population\":40222.503},{\"name\":\"Israel\",\"population\":8655.541},{\"name\":\"Jordan\",\"population\":10203.14},{\"name\":\"Kuwait\",\"population\":4270.563},{\"name\":\"Lebanon\",\"population\":6825.442},{\"name\":\"Oman\",\"population\":5106.622},{\"name\":\"Qatar\",\"population\":2881.06},{\"name\":\"Saudi Arabia\",\"population\":34813.867},{\"name\":\"State of Palestine\",\"population\":5101.416},{\"name\":\"Syria\",\"population\":17500.657},{\"name\":\"Turkey\",\"population\":84339.067},{\"name\":\"United Arab Emirates\",\"population\":9890.4},{\"name\":\"Yemen\",\"population\":29825.968},{\"name\":\"Kazakhstan\",\"population\":18776.707},{\"name\":\"Kyrgyzstan\",\"population\":6524.191},{\"name\":\"Tajikistan\",\"population\":9537.642},{\"name\":\"Turkmenistan\",\"population\":6031.187},{\"name\":\"Uzbekistan\",\"population\":33469.199},{\"name\":\"Afghanistan\",\"population\":38928.341},{\"name\":\"Bangladesh\",\"population\":164689.383},{\"name\":\"Bhutan\",\"population\":771.612},{\"name\":\"India\",\"population\":1380004.385},{\"name\":\"Iran\",\"population\":83992.953},{\"name\":\"Maldives\",\"population\":540.542},{\"name\":\"Nepal\",\"population\":29136.808},{\"name\":\"Pakistan\",\"population\":220892.331},{\"name\":\"Sri Lanka\",\"population\":21413.25},{\"name\":\"China\",\"population\":1439323.774},{\"name\":\"China, Hong Kong SAR\",\"population\":7496.988},{\"name\":\"China, Macao SAR\",\"population\":649.342},{\"name\":\"Taiwan*\",\"population\":23816.775},{\"name\":\"Dem. People's Republic of Korea\",\"population\":25778.815},{\"name\":\"Japan\",\"population\":126476.458},{\"name\":\"Mongolia\",\"population\":3278.292},{\"name\":\"Korea, South\",\"population\":51269.183},{\"name\":\"Brunei\",\"population\":437.483},{\"name\":\"Cambodia\",\"population\":16718.971},{\"name\":\"Indonesia\",\"population\":273523.621},{\"name\":\"Laos\",\"population\":7275.556},{\"name\":\"Malaysia\",\"population\":32365.998},{\"name\":\"Myanmar\",\"population\":54409.794},{\"name\":\"Philippines\",\"population\":109581.085},{\"name\":\"Singapore\",\"population\":5850.343},{\"name\":\"Thailand\",\"population\":69799.978},{\"name\":\"Timor-Leste\",\"population\":1318.442},{\"name\":\"Vietnam\",\"population\":97338.583},{\"name\":\"Anguilla\",\"population\":15.002},{\"name\":\"Antigua and Barbuda\",\"population\":97.928},{\"name\":\"Aruba\",\"population\":106.766},{\"name\":\"Bahamas\",\"population\":393.248},{\"name\":\"Barbados\",\"population\":287.371},{\"name\":\"Bonaire, Sint Eustatius and Saba\",\"population\":26.221},{\"name\":\"British Virgin Islands\",\"population\":30.237},{\"name\":\"Cayman Islands\",\"population\":65.72},{\"name\":\"Cuba\",\"population\":11326.616},{\"name\":\"Curaçao\",\"population\":164.1},{\"name\":\"Dominica\",\"population\":71.991},{\"name\":\"Dominican Republic\",\"population\":10847.904},{\"name\":\"Grenada\",\"population\":112.519},{\"name\":\"Guadeloupe\",\"population\":400.127},{\"name\":\"Haiti\",\"population\":11402.533},{\"name\":\"Jamaica\",\"population\":2961.161},{\"name\":\"Martinique\",\"population\":375.265},{\"name\":\"Montserrat\",\"population\":4.999},{\"name\":\"Puerto Rico\",\"population\":2860.84},{\"name\":\"Saint Barthélem\",\"population\":9.885},{\"name\":\"Saint Kitts and Nevis\",\"population\":53.192},{\"name\":\"Saint Lucia\",\"population\":183.629},{\"name\":\"Saint Martin (French part)\",\"population\":38.659},{\"name\":\"Saint Vincent and the Grenadines\",\"population\":110.947},{\"name\":\"Sint Maarten (Dutch part)\",\"population\":42.882},{\"name\":\"Trinidad and Tobago\",\"population\":1399.491},{\"name\":\"Turks and Caicos Islands\",\"population\":38.718},{\"name\":\"United States Virgin Islands\",\"population\":104.423},{\"name\":\"Belize\",\"population\":397.621},{\"name\":\"Costa Rica\",\"population\":5094.114},{\"name\":\"El Salvador\",\"population\":6486.201},{\"name\":\"Guatemala\",\"population\":17915.567},{\"name\":\"Honduras\",\"population\":9904.608},{\"name\":\"Mexico\",\"population\":128932.753},{\"name\":\"Nicaragua\",\"population\":6624.554},{\"name\":\"Panama\",\"population\":4314.768},{\"name\":\"Argentina\",\"population\":45195.777},{\"name\":\"Bolivia\",\"population\":11673.029},{\"name\":\"Brazil\",\"population\":212559.409},{\"name\":\"Chile\",\"population\":19116.209},{\"name\":\"Colombia\",\"population\":50882.884},{\"name\":\"Ecuador\",\"population\":17643.06},{\"name\":\"Falkland Islands (Malvinas)\",\"population\":3.483},{\"name\":\"French Guiana\",\"population\":298.682},{\"name\":\"Guyana\",\"population\":786.559},{\"name\":\"Paraguay\",\"population\":7132.53},{\"name\":\"Peru\",\"population\":32971.846},{\"name\":\"Suriname\",\"population\":586.634},{\"name\":\"Uruguay\",\"population\":3473.727},{\"name\":\"Venezuela\",\"population\":28435.943},{\"name\":\"Australia\",\"population\":25499.881},{\"name\":\"New Zealand\",\"population\":4822.233},{\"name\":\"Fiji\",\"population\":896.444},{\"name\":\"New Caledonia\",\"population\":285.491},{\"name\":\"Papua New Guinea\",\"population\":8947.027},{\"name\":\"Solomon Islands\",\"population\":686.878},{\"name\":\"Vanuatu\",\"population\":307.15},{\"name\":\"Guam\",\"population\":168.783},{\"name\":\"Kiribati\",\"population\":119.446},{\"name\":\"Marshall Islands\",\"population\":59.194},{\"name\":\"Micronesia (Fed. States of)\",\"population\":115.021},{\"name\":\"Nauru\",\"population\":10.834},{\"name\":\"Northern Mariana Islands\",\"population\":57.557},{\"name\":\"Palau\",\"population\":18.092},{\"name\":\"American Samoa\",\"population\":55.197},{\"name\":\"Cook Islands\",\"population\":17.564},{\"name\":\"French Polynesia\",\"population\":280.904},{\"name\":\"Niue\",\"population\":1.618},{\"name\":\"Samoa\",\"population\":198.41},{\"name\":\"Tokelau\",\"population\":1.35},{\"name\":\"Tonga\",\"population\":105.697},{\"name\":\"Tuvalu\",\"population\":11.792},{\"name\":\"Wallis and Futuna Islands\",\"population\":11.246},{\"name\":\"Belarus\",\"population\":9449.321},{\"name\":\"Bulgaria\",\"population\":6948.445},{\"name\":\"Czechia\",\"population\":10708.982},{\"name\":\"Hungary\",\"population\":9660.35},{\"name\":\"Poland\",\"population\":37846.605},{\"name\":\"Moldova\",\"population\":4033.963},{\"name\":\"Romania\",\"population\":19237.682},{\"name\":\"Russia\",\"population\":145934.46},{\"name\":\"Slovakia\",\"population\":5459.643},{\"name\":\"Ukraine\",\"population\":43733.759},{\"name\":\"Channel Islands\",\"population\":173.859},{\"name\":\"Denmark\",\"population\":5792.203},{\"name\":\"Estonia\",\"population\":1326.539},{\"name\":\"Faroe Islands\",\"population\":48.865},{\"name\":\"Finland\",\"population\":5540.718},{\"name\":\"Iceland\",\"population\":341.25},{\"name\":\"Ireland\",\"population\":4937.796},{\"name\":\"Isle of Man\",\"population\":85.032},{\"name\":\"Latvia\",\"population\":1886.202},{\"name\":\"Lithuania\",\"population\":2722.291},{\"name\":\"Norway\",\"population\":5421.242},{\"name\":\"Sweden\",\"population\":10099.27},{\"name\":\"United Kingdom\",\"population\":67886.004},{\"name\":\"Albania\",\"population\":2877.8},{\"name\":\"Andorra\",\"population\":77.265},{\"name\":\"Bosnia and Herzegovina\",\"population\":3280.815},{\"name\":\"Croatia\",\"population\":4105.268},{\"name\":\"Gibraltar\",\"population\":33.691},{\"name\":\"Greece\",\"population\":10423.056},{\"name\":\"Holy See\",\"population\":0.809},{\"name\":\"Italy\",\"population\":60461.828},{\"name\":\"Malta\",\"population\":441.539},{\"name\":\"Montenegro\",\"population\":628.062},{\"name\":\"North Macedonia\",\"population\":2083.38},{\"name\":\"Portugal\",\"population\":10196.707},{\"name\":\"San Marino\",\"population\":33.938},{\"name\":\"Serbia\",\"population\":8737.37},{\"name\":\"Slovenia\",\"population\":2078.932},{\"name\":\"Spain\",\"population\":46754.783},{\"name\":\"Austria\",\"population\":9006.4},{\"name\":\"Belgium\",\"population\":11589.616},{\"name\":\"France\",\"population\":65273.512},{\"name\":\"Germany\",\"population\":83783.945},{\"name\":\"Liechtenstein\",\"population\":38.137},{\"name\":\"Luxembourg\",\"population\":625.976},{\"name\":\"Monaco\",\"population\":39.244},{\"name\":\"Netherlands\",\"population\":17134.873},{\"name\":\"Switzerland\",\"population\":8654.618},{\"name\":\"Bermuda\",\"population\":62.273},{\"name\":\"Canada\",\"population\":37742.157},{\"name\":\"Greenland\",\"population\":56.772},{\"name\":\"Saint Pierre and Miquelon\",\"population\":5.795},{\"name\":\"US\",\"population\":331002.647}]","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"payload~> | $ | (\t    $cur_country := $.country_region;\t    $population := $$.countries[name = $cur_country  ].population;\t    {\t     \"population_in_million\" : $population/1000~>$round(2),\t     \"new_cases_per_million\" : new_cases_current*1000/$population ~>$round(2),\t     \"total_cases_per_million\" : total_conf_cases*1000/$population ~>$round(0)\t     } ) |\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":340,"wires":[["8637a5d1.94f4c8","b58129cd.f46a38"]]},{"id":"8637a5d1.94f4c8","type":"debug","z":"3739886a.1825b8","name":"population,... added","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":340,"wires":[]},{"id":"737a23b5.a584ec","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"passthru":false,"label":"retrieve","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":170,"y":20,"wires":[["48cc765f.3e0de8","bd53b32d.740eb"]]},{"id":"e3654952.086598","type":"ui_text","z":"3739886a.1825b8","group":"50d8c10e.e27b4","order":2,"width":0,"height":0,"name":"","label":"","format":"{{msg.payload}}","layout":"row-spread","x":1050,"y":100,"wires":[]},{"id":"bd53b32d.740eb","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"retrieving data ...","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":20,"wires":[["e3654952.086598"]]},{"id":"4ccd263e.f616c8","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"ui_table","pt":"flow","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"\"date \" & last_date_retrieved & \" retrieved\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":460,"wires":[["e3654952.086598"]]},{"id":"4a582d99.bd4984","type":"ui_toast","z":"3739886a.1825b8","position":"dialog","displayTime":"20","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"","raw":true,"topic":"","name":"","x":490,"y":700,"wires":[[]]},{"id":"f721729f.198f","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":4,"width":0,"height":0,"passthru":false,"label":"info","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":80,"y":700,"wires":[["be40b73d.9caab8"]]},{"id":"be40b73d.9caab8","type":"template","z":"3739886a.1825b8","name":"","field":"payload","fieldType":"msg","format":"html","syntax":"plain","template":"<head>\n<style>\ntable {\n  font-family: arial, sans-serif;\n  border-collapse: collapse;\n  width: 100%;\n}\n\ntd, th {\n  border: 1px solid #dddddd;\n  text-align: left;\n  padding: 8px;\n}\n\ntr:nth-child(even) {\n  background-color: #dddddd;\n}\n</style>\n</head>\n<body>\n<table>\n    <tr>\n        <th>column</th>\n        <th>field</th>\n        <th>description</th>\n    </tr>\n    <tr>\n        <td>1</td>\n        <td>location</td>\n        <td>country / state</td>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>distance</td>\n        <td>distance in km from the country you have selected in the dropdown menu.<br>\n            -1 if no country is selected.\n        </td>\n    </tr>\n    <tr>\n        <td>3</td>\n        <td>cases/day/million</td>\n        <td>average number of confirmed cases per day (over the last 7 days) per million persons.<br>\n            we have taken an average over the last 7 days to filter out any weekend effects.\n        </td>\n    </tr>\n    <tr>\n        <td>4</td>\n        <td>RN (Repr. Nr)</td>\n        <td>Reproduction number which is defined as <br><i>(conf_cases_last_week / conf_cases_week_before)</i><br>\n            where <i>conf_cases_last_week</i> is the total number of new confirmed cases registered last week.\n            A week is chosen to filter out any weekend effects.<br>\n            A RN of 9999 indicates that previous week no cases were reported while this week at least one case has been reported.\n        </td>\n    </tr>\n    <tr>\n        <td>5</td>\n        <td>RN (-1 day)</td>\n        <td>same as previous column but then calculated for the previous day.  \n            So tomorrow this column shows the same value you can see today in previous column.\n        </td>\n    </tr>\n    <tr>\n        <td>6</td>\n        <td>RN (-1week)</td>\n        <td>same as 3rd column but then calculated for the previous week.  \n            So a week later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>7</td>\n        <td>RN (-2week)</td>\n        <td>same as 3rd column but then calculated for 2 weeks ago.  \n            So 2 weeks later this column will show the same value as you can see today in 3rd column.\n        </td>\n    </tr>\n    <tr>\n        <td>8</td>\n        <td>RN (-3week)</td>\n        <td>same as 3rd column but then calculated for 3 weeks ago.  \n        </td>\n    </tr>\n    <tr>\n        <td>9</td>\n        <td>tot. cases/million</td>\n        <td>total confirmed cases (at date retrieved) per million.  So this includes all confirmed cases.\n        </td>\n    </tr>\n    <tr>\n        <td>10</td>\n        <td>popul. [million]</td>\n        <td>total country population expressed in million.\n        </td>\n    </tr>\n    <tr>\n        <td>11</td>\n        <td>tot. cases</td>\n        <td>total reported confirmed cases.\n        </td>\n    </tr>\n</table>\n</body>\n","output":"str","x":260,"y":700,"wires":[["4a582d99.bd4984"]]},{"id":"b58129cd.f46a38","type":"change","z":"3739886a.1825b8","name":"html with background color","rules":[{"t":"set","p":"payload","pt":"msg","to":"( /* 3 color gradient green (0,255,0) - yellow (255, 255, 0) - red (255,0,0) \t     note that class tag is added as a hack to assure that sorting is workign properly\t   */\t  $rep_nr_with_style_fn := function($rep_nr){ \t       \"<div class='J\" & $substring($string($rep_nr/1000),2) &\t       \"' style='background-color: rgb(\" &\t       ( $rep_nr <  0.6 ? \"0,255,0\"\t         : $rep_nr <= 1.0 ? (\t             $fraction255 := (($rep_nr - 0.6)*255/0.4)~>$round(0);\t             $fraction255 & \",\" & \t             ((255 - ( $fraction255*(255-255)/255))~>$round(0)) & \",0\";\t         )\t         : $rep_nr <= 2.0 ? (\t             $fraction255 := (($rep_nr - 1)*255)~>$round(0);\t             \"255,\" & (255 - $fraction255)  & \",0\";\t         )\t         : \"255,0,0\") & \t         \")'>\" & $rep_nr & \"</div>\"         \t  };\t  payload ~> | $ | {\t     \"html_reproduction_number\"          :  $rep_nr_with_style_fn(reproduction_number),\t     \"html_reproduction_number_1day_ago\" :  $rep_nr_with_style_fn(reproduction_number_1day_ago),\t     \"html_reproduction_number_1week_ago\" :  $rep_nr_with_style_fn(reproduction_number_1week_ago), \t     \"html_reproduction_number_2week_ago\" :  $rep_nr_with_style_fn(reproduction_number_2week_ago),\t     \"html_reproduction_number_3week_ago\" :  $rep_nr_with_style_fn(reproduction_number_3week_ago)\t  } |\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":400,"wires":[["e9092edc.7f8d3","4ccd263e.f616c8","94e3c9a.18b3d38"]]},{"id":"94e3c9a.18b3d38","type":"debug","z":"3739886a.1825b8","name":"with background color","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":400,"wires":[]},{"id":"631625f5.c0ae7c","type":"ui_dropdown","z":"3739886a.1825b8","name":"","label":"country","tooltip":"","place":"Select option","group":"50d8c10e.e27b4","order":3,"width":0,"height":0,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":580,"y":280,"wires":[["b82fe468.2f9e68","e3654952.086598"]]},{"id":"b82fe468.2f9e68","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"country","pt":"flow","to":"{\t  \"name\" : payload,\t  \"latitude\" : $flowContext(\"ui_table\")[country_region = $$.payload].latitude,\t  \"longitude\" : $flowContext(\"ui_table\")[country_region = $$.payload].longitude\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":280,"wires":[["188cf083.a11a5f"]]},{"id":"188cf083.a11a5f","type":"change","z":"3739886a.1825b8","name":"msg.payload = flow.ui_table","rules":[{"t":"set","p":"payload","pt":"msg","to":"ui_table","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":940,"y":340,"wires":[["f4d0f6f8.2e6178"]]},{"id":"f4d0f6f8.2e6178","type":"split","z":"3739886a.1825b8","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":400,"wires":[["eb8e08a0.6168a8"]]},{"id":"eb8e08a0.6168a8","type":"function","z":"3739886a.1825b8","name":"calculate distance","func":"var lat1 = flow.get(\"country\").latitude;\nvar lon1 = flow.get(\"country\").longitude;\nvar lat2 = msg.payload.latitude;\nvar lon2 = msg.payload.longitude;\n\n/* calculate distance as specified in \n   https://www.movable-type.co.uk/scripts/latlong.html */\n   \nconst R = 6371e3; // metres\nconst φ1 = lat1 * Math.PI/180; // φ, λ in radians\nconst φ2 = lat2 * Math.PI/180;\nconst Δφ = (lat2-lat1) * Math.PI/180;\nconst Δλ = (lon2-lon1) * Math.PI/180;\n\nconst a = Math.sin(Δφ/2) * Math.sin(Δφ/2) +\n          Math.cos(φ1) * Math.cos(φ2) *\n          Math.sin(Δλ/2) * Math.sin(Δλ/2);\nconst c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));\n\nconst d = R * c; // in metres\n\nmsg.payload.distance = Math.round(d / 1000) ;\n\nreturn msg;","outputs":1,"noerr":0,"x":930,"y":440,"wires":[["e9fe4c33.8355","61e0af74.21009"]]},{"id":"e9fe4c33.8355","type":"join","z":"3739886a.1825b8","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":890,"y":480,"wires":[["e9092edc.7f8d3"]]},{"id":"61e0af74.21009","type":"debug","z":"3739886a.1825b8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":440,"wires":[]}]

Here below a chart where "United Kingdom" is selected and we have sorted according increasing distance:

Weirdly, this is all I see when I load the tool in dashboard?

Did you press the 'Retreive' button?

Maybe this is because you need to install node node-red-node-ui-table.

I would suggest to install this node... and if this is not sufficient then throw away the flow and add the flow again.

Already installed :frowning:

yep!

did you try the part after the 3 points ?

I just tested it using the latest version I have posted in post 9= COVID-19 status by country - #9 by janvda and this is working for me.

yep. I did. Thanks. To be honest I think there's something odd going on with my dashboard because this also doesn't load properly for me. I suspect there's something intrinsic missing from my dashboard install that's causing these two problems. Haven't a clue where to begin debugging though!

I tried it last night and got the same result as @dclear.
I noticed that the table size was ringed in red as though it was having a problem with the size.
I didn't have time to look further, so I deleted the flow.
So yes, I can confirm the problem.

@Paul-Reed, @dclear

  • what browser are you using?
  • is the machine running NR the same one the browser is on?
  • what machine is NR running on?
  • What version is NR and node.js?

I'm running NR on a mac and Safari, Chrome and FireFow all work.
I then installed the flow on a Pi3 model-B (Buster) and accessed it via the browser on my mac and it works fine

@janvda - a thought, how about a column showing the one day change in RN?

@zenofmud

  • what browser are you using?
    Chrome Version 83.0.4103.106 (Official Build) (64-bit)

  • is the machine running NR the same one the browser is on?
    No

  • what machine is NR running on?
    NR running in Oracle cloud VM, on Oracle Linux 4.14

  • What version is NR and node.js?
    Node-RED version: v1.0.6
    Dashboard version 2.22.1
    Node.js version: v10.20.1

I think I managed to reproduce the issue.
To fix this I had to do 2 things:

  1. Assure that the table node is set to the right Group:

  2. I also had to restart my node-red docker container (just redeploying the flow didn't fix it). I guess that restarting node-red is sufficient. (I think this is needed in case you install node-red-node-ui-table via the "manage palette" menu).

Sorry Paul, not fully clear what you mean by one day change in RN. You mean the difference between column 4 and 5 ?

Upon importing the flow the table group was set correctly - [COVID19 - country overview] table, but reselected (the same group) again, deploy, and thereafter the flow worked OK.
Nice work @janvda

1 Like