topic Re: Filter Expression using Column with List of Values in App Development

Filter Expression using Column with List of Values

estebanafonso — Tue, 24 Nov 2020 16:29:30 GMT

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?

Re: Filter Expression using Column with List of Values

QFabian — Tue, 24 Nov 2020 20:31:27 GMT

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;

Re: Filter Expression using Column with List of Values

estebanafonso — Mon, 30 Nov 2020 18:11:16 GMT

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