Set Analysis for fetching the value with maximum count in the data

[Vivek12]

I have a data set as follows-

Field 1	Field 2	Count of Occurrence of the values in Field 2
Plant 1	A1	137
	B1	4
Plant 2	A2	2
	B2	176
Plant 3	A3	593
Plant 4	A4	410
	B4	2
	C4	1
Plant 5	A5	405
	B5	582
	C5	2

In my output I want the values from the field 2 with relatively maximum count of occurence . Output should be as follows

Field 1	Field 2
Plant 1	A1
Plant 2	B2
Plant 3	A3
Plant 4	A4
Plant 5	A5 & B5

Can anyone help me with writing the set analysis for the same?
I don't expect too many cases as for Plant 5 in my data set so we can treat it separately using IF condition

Thanks in Advance

[JHuis]

Can you tell me; for everything you use the max value, but for plant 5 you want 2? 

Do you just want to see the plants with Field 2>150?

Field 2

[Vivek12]

The exact requirement is that I need the values in field 2 with relatively count of occurence. I know this sounds a bit ambiguous but that's how the situation is.

You can ignore this case and just help me with set analysis for extracting field2 data with maximum count of occurrence only

Thanks

[JHuis]

firstsortedvalue ([Field 2],- [Count of Occurrence of the values in Field 2])

[Vivek12]

Thanks @JHuis 

Can you share the set analysis for calculating the count of occurrence?
I was using the aggr function to calculate this but am running into error

[JHuis]

But you got an field occurence? 

[Vivek12]

No it's not part of the original data. This is what I put in by myself in excel version for explaining the requirement

I need to write set expression for it to get the count of occurence

[Vivek12]

The issue is still open
Any help on this would be highly appreciated!

[Cascader]

maybe not the optimal solution, but this what come in my mind:

abc:
load * Inline [
f1,f2,f3
Plant1, A1, 137
Plant1, B1, 4
Plant2, A2, 2
Plant2, B2, 176
];

Left join (abc)
load

f1 ,
max(f3) as isMax
Resident abc
Group by f1;

abcd:
load
f1,f2
Resident abc
Where f3 = isMax;
DROP Table abc;

you can use the set analysis easier in abcd table if you want to remove condition Where f3 = isMax;

[Vivek12]

Thanks @Cascader 

unfortunately we can't modify anything in the script or load editor as per the client requirement

So we need to do this only using set analysis. Do you think it would be doable using set analysis only?

PS- I am relatively new to Qlik Sense so ignore my naivety if I am missing some point over here 😄