Merging Locations based on data

[SariPari]

Hi - I have a below scenario where I want to check if the locations can be flagged based on the data scenario.

ID	City	Desk	Emp	Flag
1	Seattle	1000	500
2	Seattle	100	500	Yes
3	Seattle	500	100	Yes
1	Dallas	100	50
2	Dallas	100	25	Yes

I have to look at the ID get the Max Desk and see if the Total Emp by City can fit into Max Desk ID and if not I have to look at the Emp where ID's can fit into Max Desk ID.

EX: Seattle -

In the above example - The total Emp for saettle is 1,100 and Max Deskis 1,000,So Total Emp is Greater than Max Desk, so I want to check the next scenario where I have to pick locations where total Emp is < Max Desk...here ID 2 + 3 Emp will be < Max Desk, So it will have a value of  'Yes',

Ex: Dallas - Here Total Emp is 75 & Max Deskis 100, so Total Emp is less than Max Desk, so the Flag will be 'Yes'

is this doable ?

Thank you so much

@Lech_Miszkiewicz @Anil_Babu_Samineni @theoat @TomBond77 @marcus_sommer @sunny_talwar

[marcus_sommer]

You could calculate the totals + max in beforehand like:

m1: mapping load City, sum(Emp) from Source group by City;
m2: mapping load City, max(Emp) from Source group by City;

and then querying the total/max like: applymap('m1', City, null()) probably embedded within one or several if-loops comparing the values against all wanted conditions.

[Anil_Babu_Samineni]

@SariPari So, ID=1 also matches the scenario that you explained for Seattle, why not flag with Yes?