Create grouped field name in script

[iczkla]

Hi All,

I am looking for a solution which would allow me to somehow group field in a script. I am showing number of workers per country and do not want to show separate country name if its headcount is less than 3 people.

I have managed to achieve this by creating custom dimension as below:

if(aggr(Count([Employee ID]),Country)<3,'Other',Country)

However user is still able to identify which countries belong to this 'Other' category as filter selection on the country field appears on the top of the sheet. 

I thought I should somehow achieve same result by using grouping in the script as below:

MYTABLE:

LOAD

ID | Country

E1 | China

E2 | China

E3 | China

E4 | India

E5 | India

E6 | Japan

E7 | Japan

LEFT JOIN (MYTABLE)
Load
 ID,
 If(count([ID])<3,Country,'Other') as Country

Resident MYTABLE
Group By ID;

Drop Field Country from MYTABLE;

but I am keep getting invalid expression error. Does anyone have any idea if it's possible to achieve this result in script?

[marcus_sommer · Accepted solution]

The count and the check must be done in two separate steps. Beside this your aggregation needs to be reversed in regard to the included fields. I would probably going in this direction:

m: mapping load Country, count(ID) from Source group by Country;

t: load ID, if(applymap('m', Country, 0) < 3, 'Other', Country) as Country from Source;

- Marcus 

[iczkla]

Worked like a charm, thank you Marcus!