Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
@marcus_sommer - Thank you.
Question - What is Peek ('new') lookign at ? I tried the above..PFA QVF.
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