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.
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.
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
