Integrations

How to show data from two related fields in a Chart Builder visualization

There is a query called High and low temperatures on the dates of Bigfoot sightings in the Monsters project that returns a simple table with three columns:

Screen_Shot_2018-12-27_at_5.55.05_PM.png

Click on the Chart icon above the results to build a quick visualization from the query results. Set the X axis to date, the Y axis to temperature_low, and you have a visualization, but where do you put temperature_high?:

Screen_Shot_2018-12-27_at_9.51.04_PM.png

Looking at the data, the solution is to put both the high temp and the low temp values in the same column and call it temperature, and to have another column called temp_value that would indicate whether the temp shown is a high temp or a low temp for the day. Fortunately, this kind of data reorganization where columns get collapsed into rows is what the SQL UNPIVOT command does. Here is the original query rewritten to use UNPIVOT to collapse the high and low temp columns into one column, and the resulting table:

Screen_Shot_2018-12-27_at_10.17.25_PM.png

Select Chart to use Chart Builder on the results of the query, set the marks to Circle, the X axis to date, the Y axis to temperature, the color to temp_type, resize the chart toto 640 X 700, and you'll have this visualization:

Screen_Shot_2018-12-27_at_10.23.44_PM.png