Custom mapping does not work with summarized data

Dear all,

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.

Thanks for your time and your help

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):

    max(`Year`) AS `Year`,
    `iso3` AS `Country`,
    max(`Value`) AS `Value`,
      `countries`.`title_en` AS `Country`,
      year(`date`) AS `Year`,
      `terms` AS `Indicator`,
      `indicator` AS `Response`,
        WHEN value = 12433 THEN "No"
        WHEN value = 12434 THEN "Yes"
      END AS `Value`,
      `value` as `Result`,
      countries.iso_3 AS `iso3`
      LEFT JOIN countries ON db.`countries` = countries.`iso_2`
     `Terms` = {{Terms}}
  ) AS `source`
  `Country` ASC

and here a screenshot (indicators are hidden) :

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 = and country_year.year = xyz.year

Thanks, I agree on this. but right now my biggest problem is that the custom mapping is lost in any case :frowning: and I need it in the region map

Sorry, I misunderstood.

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?

Same thing. I can think of 3 cases:

  • do not use aggregation and get data straight from the model: I get the custom_mapping values but not the wanted results
  • use an aggregation (i.e. max()): it loses the custom_mapping and I get the wanted results
  • use SQL with CASE: I get the wanted results and the custom_mapping (via CASE), but I cannot use the field as a metric field

Obviously before to post, I upgraded to 1.49.8 on mariaDB