I have been fighting with this moving target for a while now. I have read all other topics etc etc without finding a solution. I have a very simple query that I would like to display on a region map BUT:
the data I have to use for metric, is an integer that I would like to show with a label (using custom mapping)
Everything works great, I can do that easily as you can see here:
However, you can note that there is something strange in the result, and this is because I need to aggregate some results per year, taking the max(year) . For example from:
Country: France, Year: 2019, Indicator: 1, Value: 12433
Country: France, Year: 2022, Indicator: 1, Value: 12434
I have to select the second one. This can be achieved, as I said, using the summarize function and selecting max(year).
The point is that once I do that, there is no way I can get back my values from the custom mapping:
12433: "No"
12434: "Yes"
I tried rewriting the query in SQL, doing all type of operation including CASE WHEN. The closest thing would be selecting max(value), but then I lose the custom mapping too.
You need to use a WINDOW function in SQL. Not the easiest syntax to work with but it does a great job.
Support depends upon your database. Postgres has good support. If MS SQL, you need a relatively recent compatability level. If MySQL, time to get a proper database.
Thanks @AndrewMBaines , but I am using the Max function and, although the result is good, then metabase does not allow me to select that value as the metric for the map. This is my SQL code (yeah, yeah, mariadb):
SELECT
max(`Year`) AS `Year`,
`iso3` AS `Country`,
`Indicator`,
max(`Value`) AS `Value`,
`Result`
FROM
(
SELECT
`countries`.`title_en` AS `Country`,
year(`date`) AS `Year`,
`terms` AS `Indicator`,
`indicator` AS `Response`,
CASE
WHEN value = 12433 THEN "No"
WHEN value = 12434 THEN "Yes"
END AS `Value`,
`value` as `Result`,
countries.iso_3 AS `iso3`
FROM
edp002
LEFT JOIN countries ON db.`countries` = countries.`iso_2`
WHERE
`Terms` = {{Terms}}
) AS `source`
GROUP BY
`Country`,
`Indicator`
ORDER BY
`Country` ASC
That will give you the most recent year and the highest value (not necessarily in the most recent year).
If you can't use a Window function, you could do it like this (not your table/column names, but you'll get the idea):
with country_year AS
(
Select MAX(year) as yr, country from xyz
group by country
)
select country, score, year
from xyz
inner join country_year on country_year.country = xyz.country and country_year.year = xyz.year
I've not worked with maps much (I just push any map stuff to a Google maps integration).
What happens if you convert the query to a model, then define the data types in the model?
and I need it in the region map