IF ELSE IF in a loop

[SariPari]

I have a below scenario ex where in 1 loaction there are multiple buildings and I want to calculate the New EMP count by merging the FTE's based on the capacity of other buildings

Bldg	City	Desk	Emp	New EMP
1	Seattle	1000	500	1000
2	Seattle	500	400	0
3	Seattle	500	200	200
4	Seattle	200	100	0

Ex: Seattle has 4 buildings and Bldg 1 has 1000 desks and 500 Emp..I can move Employees from Bldg 2 & 4 to Building 1...so the new EMP Count will be 1000 for bldg 1 and BLDG 2 & 4 will be 0 and Bldg 3 will remain the same.

Another EX:

Bldg	City	Desk	Emp	New EMP
1	Dallas	1000	500	900
2	Dallas	500	300	0
3	Dallas	500	100	0

I can move employees from Both Bldg 2 & Bldg 3 to Bldg 1 as there are enough desks to accomodate employess from all the buildings.

Is this doable at all ? 

@Anil_Babu_Samineni @swuehl @sunny_talwar

[marcus_sommer]

I think you could use interrecord-functions and/or various joined aggregation-results within nested if-loops to create useful data-structures for the final insights, for example:

load *, if(City = previous(City), if([Desk-Emp] < peek('New'),
              peek('New') - EMP, peek('New'))) as New
resident X order by City, [Desk-Emp] desc;

whereby [Desk-Emp] is the difference between both fields and created within a previous load. Depending on your real data-set and requirements you may need to include some further conditions and/or performing this several times by ascending and descending orders and/or after adding other useful information, like:

join(MyTable)

load City, sum(Desk) as DeskTotal, sum(Emp) as EmpTotal
resident MyTable group by City;

and also with min/max/avg/count/rank to calculate rates or other helpful stuff in regard to distribute the employees. Just add these possibilities step by step and play with the results.  

[SariPari]

@marcus_sommer  - Thank you.

Question - What is Peek ('new') lookign at ? I tried the above..PFA QVF.

[marcus_sommer]

New is the new created field which contained the final or maybe an intermediate result of the various checks whereby peek('New') refers to it's own within the previous record:

if(City = previous(City), if([Desk-Emp] < peek('New'),
              peek('New') - EMP, peek('New'))) as New