Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm working on a requirement where i have to :
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 |
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;
Its not clear what you mean by "swap". Do you want to manipulate the data during load? If you want to do this in the front end, provide some detail on the object yous are using (pivot table, bar chart etc) and exactly what output you want for the given input.
Also I don't see four values for A or B at some time values. How do you want to handle this case?
Hi, Sorry for confusion caused.
I want to do this in script. I want to apply the logic for hour - 9.
If 4 SKUs are not available, i want the logic to take whatever is available. In this case, 2 SKUs.
The logic should take SKU - 1 and 3 from Area 'A' (as they are high qty) and SKU - 12 and 13 (as they are less qty) from Area 'B' and then swap their locations.
So, final O/P would be like below highlighted. Please help.
Area | Hour | Location | SKU |
A | 7 | A-4 | 4 |
A | 8 | A-2 | 2 |
A | 8 | A-4 | 4 |
A | 9 | A-1 | 12 |
A | 9 | A-3 | 13 |
A | 10 | A-3 | 3 |
A | 11 | A-3 | 3 |
B | 9 | B-1 | 5 |
B | 9 | B-2 | 6 |
B | 9 | B-8 | 1 |
B | 9 | B-9 | 3 |
B | 9 | B-10 | 14 |
B | 9 | B-11 | 15 |
B | 10 | B-3 | 7 |
B | 11 | B-4 | 8 |
B | 12 | B-5 | 9 |
B | 13 | B-6 | 10 |
B | 14 | B-7 | 11 |
@sunny_talwar Any help you do is highly appreciated 🙂
I am not sure I understand the logic here.... A-1 has multiple rows in the input, but within output we don't have Qty and the multiple rows become a single row? Does that mean that we don't need Qty and Order field to be part of the output?
Also, you initially mentioned highest 2 Qty items of Area 'A' with lowest 4 items of Area 'B'? What 4 items are we talking about? It seems you did a 2 - 2 swap? And what was the logic to assign A-1 with SKU 12 and A-3 with SKU 13? What if B had another SKU with Qty 3... let's say 14... now which ones will be swaped? 12, 13 or 12, 14 or 13, 14?
Actually, there are 3 Area B with 1 Qty... 7, 12, 13... why did you not include 7 in your swap?
Yes. we don't need Order and qty in output. They are used to calculate sum(qty) by location in the area in the logic.
To swap, we are comparing A Vs B.
In this example, i only have 2 items at Hour 9 in 'A'.
So, i take those 2 highest items swap it with lowest 2 items in 'B' with lowest qty at '9'
Because SKU '7' transaction has occurred at Hour - '10'.
I only want to look at transactions happened at Hour - '9'
Okay, now why are we doing this for only Hour 9? What about if there was an hour 10? Would you want to do it from Hour 9 and Hour 10 or just Hour 9 or just Hour 10? Also, what if there was another Qty 1 in Area B for Hour 9? What would you have done then?
Sorry, I didn't realize that you already have hour 10 and 11... so this seems like a very specific logic for hour 9. That works, but what if there were three Qty 1s in Area B? against 2 values in Area A.