Swapping highest value with lowest value

[markgraham123]

Hi all,

I'm working on a requirement where i have to :

• Select only Hour - '9' and swap SKUs of highest 2 Qty items of Area 'A' with lowest 4 items of Area 'B' 
• example is exampled below:

In below example at Hour - '9',     Area 'A' SKUs - 1 and 3 has highest qty.  So, i want to swap them with SKUs 13 and 14 from Area B. Below is sample data.

Attached is the QV file with data. Any help is highly appreciated. Please advise.

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

[markgraham123]

If there is another Qty '1' in Area 'B',  it can take any two from Area 'B' or it would be great of it can take min(location) from Area 'B'.

For e.g. if it has B-1, B-2, and B-3. I would like to take B-1 and B-2.

[sunny_talwar · Accepted solution]

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;

[markgraham123]

Sunny - 

You did the magic again... 🙂 It works...

How do i also give new column 'Rank' in the final output, such that, i can see which is priority 1 and so on..

Rank depends on the qty from Area 'A'. High qty gets Rank1 and so on....

[sunny_talwar]

By bringing it into the Final Table

Final:
LOAD Area,
	 Hour,
	 Location,
	 Rank,
	 If(Len(Trim(SKU_A)) > 0, If(Area = 'A', SKU_B, SKU_A), SKU) as SKU
Resident Temp;

DROP Table Temp;

[markgraham123]

Sunny,

Sorry for the confusion..

I understand existing 'Rank' field provides rank, but I just want to give rank to the SKUs we swapped..

For the SKUs, we didn't swap, rank can be null.

[sunny_talwar]

May be this

Final:
LOAD Area,
	 Hour,
	 Location,
	 If(Len(Trim(SKU_A)) > 0, Rank) as Rank,
	 If(Len(Trim(SKU_A)) > 0, If(Area = 'A', SKU_B, SKU_A), SKU) as SKU
Resident Temp;

DROP Table Temp;

[markgraham123]

Thanks a ton, Sunny!