Filter Expression using Column with List of Values

[estebanafonso]

Input Table

Unique Case Id	Country	Value	Benchmark Countries
A	US	5	"US", "France"
X	US	3	"US", "France"
Z	France	2	"Japan", "US", "Germany"
Y	Germany	3	"China"
G	China	6	"US", "Germany"
F	China	1	"US", "Germany"
N	China	2	"US", "Germany"
L	Japan	3	"US"

Aggregated Table

Country	Benchmark Countries	Benchmark Sum(Values)
US	"US", "France"	10
France	"Japan", "US", "Germany"	14
Germany	"China"	9
China	"US", "Germany"	11
Japan	"US"	8

I have a table with a similar structure to the first.  What I need to do, in essence, is to write expression that creates the 3 column in 2nd table, where for each countries it produces the sum of the values from that country's benchmark countries.  For example for France benchmark countries are Japan, US, and Germany, so result is 14 (3+5+3+3).  The expressions I have been tinkering with tend to like the one below but I whatever I try I can not get it to work correctly. 

SUM({$<Country={$(=MaxString([Benchmark Countries]))}>} Aggr(SUM(TOTAL <Country> Value), Country))

 Any suggestions?

[QFabian · Accepted solution]

Hi @estebanafonso , here an example that you can use if you want to solve it by script :

Data:
load * inline [
Unique Case Id, Country,Value,Benchmark Countries
A, US, 5, "US,France"
X, US, 3, "US,France"
Z, France, 2, "Japan,US,Germany"
Y, Germany, 3, "China"
G, China, 6, "US,Germany"
F, China, 1, "US,Germany"
N, China, 2, "US,Germany"
L, Japan, 3, "US"
];

Data2:
Load
   distinct
   Country,
   trim(subfield([Benchmark Countries],',')) as MyCountries
Resident Data;

left join
Load
   Country as MyCountries,
   sum(Value) as sumValue
Resident Data
group by Country;

[estebanafonso]

Hi Fabian, I played around with it, and I think this approach will get me where I need to go.  Thank you.