topic Re: Swapping highest value with lowest value in QlikView

Swapping highest value with lowest value

markgraham123 — Sat, 16 Nov 2024 20:53:23 GMT

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

Re: Swapping highest value with lowest value

jonathandienst — Mon, 13 May 2019 05:28:55 GMT

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?

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 12:28:57 GMT

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

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 12:42:29 GMT

@sunny_talwar Any help you do is highly appreciated 🙂

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 12:51:48 GMT

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?

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 12:54:08 GMT

Actually, there are 3 Area B with 1 Qty... 7, 12, 13... why did you not include 7 in your swap?

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 12:55:35 GMT

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'

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 12:57:09 GMT

Because SKU '7' transaction has occurred at Hour - '10'.

I only want to look at transactions happened at Hour - '9'

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 12:58:08 GMT

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?

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 12:59:56 GMT

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.

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 13:02:02 GMT

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.

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 13:22:08 GMT

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;

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 13:56:11 GMT

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

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 13:58:30 GMT

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;

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 14:02:14 GMT

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.

Re: Swapping highest value with lowest value

sunny_talwar — Mon, 13 May 2019 14:19:44 GMT

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;

Re: Swapping highest value with lowest value

markgraham123 — Mon, 13 May 2019 14:23:17 GMT

Thanks a ton, Sunny!