topic Create grouped field name in script in App Development

Create grouped field name in script

iczkla — Fri, 03 Jun 2022 13:10:42 GMT

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?

Re: Create grouped field name in script

marcus_sommer — Fri, 03 Jun 2022 13:26:27 GMT

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

Re: Create grouped field name in script

iczkla — Sun, 05 Jun 2022 10:50:56 GMT

Worked like a charm, thank you Marcus!