May be this
T1:
LOAD Hour,
SKU,
Sum(Qty) as Qty,
If(Area = 'A', Sum(Qty), -Sum(Qty)) as Qty_Temp,
Location,
Area
Group By Hour, SKU, Location, Area;
LOAD * INLINE [
Order, Hour, SKU, Qty, Location, Area
A, 9, 1, 22, A-1, A
B, 9, 1, 21, A-1, A
C, 9, 1, 40, A-1, A
L, 9, 1, 10, A-1, A
D, 8, 2, 12, A-2, A
E, 8, 2, 13, A-2, A
F, 8, 2, 10, A-2, A
G, 10, 3, 41, A-3, A
H, 11, 3, 45, A-3, A
I, 9, 3, 12, A-3, A
J, 7, 4, 11, A-4, A
K, 8, 4, 43, A-4, A
M, 9, 5, 10, B-1, B
N, 9, 6, 3, B-2, B
O, 10, 7, 1, B-3, B
P, 11, 8, 8, B-4, B
Q, 12, 9, 2, B-5, B
R, 13, 10, 2, B-6, B
S, 14, 11, 3, B-7, B
T, 9, 12, 1, B-8, B
U, 9, 13, 1, B-9, B
V, 9, 14, 7, B-10, B
W, 9, 15, 3, B-11, B
];
Temp:
LOAD *,
If(Area = Previous(Area) and Hour = Previous(Hour), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident T1
Order By Area, Hour, Qty_Temp desc, Location;
DROP Table T1;
Left Join (Temp)
LOAD Rank,
Hour,
SKU as SKU_A
Resident Temp
Where Area = 'A' and Hour = 9;
Left Join (Temp)
LOAD Rank,
Hour,
SKU as SKU_B
Resident Temp
Where Area = 'B' and Hour = 9;
Final:
LOAD Area,
Hour,
Location,
If(Len(Trim(SKU_A)) > 0, If(Area = 'A', SKU_B, SKU_A), SKU) as SKU
Resident Temp;
DROP Table Temp;
