topic Re: Calculate Distinct value of Rangesum in QlikView

Calculate Distinct value of Rangesum

markgraham123 — Sat, 16 Nov 2024 02:15:42 GMT

Hi All,

I want to calculate Rangesum for below column 'Weight' taking in the distinct value of order.

Code i'm using gives me Range sum by counting same value multiple times:

If(Zone= Previous(Zone) and Time = Previous(Time), RangeSum(Cum_Wt, Peek('Cum_Wt')), Weight) as Cum_Wt

Current O/P : 280

Desired Output : 70

Here is the sample data:

Can someone please help on how can i only Rangesum Weight of an Order only once?

Time	Zone	Order	SKU	Weight
7	B	7895647	1A	10
7	B	7895647	2B	10
7	B	7895647	2A	10
7	B	7895647	2C	10
8	B	8763190	3D	20
8	B	8763190	3A	20
8	B	8763190	4B	20
8	B	8763190	5E	20
9	C	7641839	1B	25
9	C	7641839	4D	25
9	C	7641839	3D	25
9	C	7641839	1K	25
10	D	8566128	2J	15
10	D	8566128	2P	15
10	D	8566128	5T	15
10	D	8566128	8U	15

Any help is highly appreciated.

Re: Calculate Distinct value of Rangesum

markgraham123 — Thu, 10 Oct 2019 13:03:13 GMT

@sunny_talwar

Re: Calculate Distinct value of Rangesum

sunny_talwar — Thu, 10 Oct 2019 14:30:21 GMT

The code you showed is not going to give you a single value... it will show a value on each row... I am not sure if you want a single value or row for each value here?

Re: Calculate Distinct value of Rangesum

markgraham123 — Thu, 10 Oct 2019 14:51:32 GMT

Here is the desired output in the last column, Sunny...

Time	Zone	Order	SKU	Weight	Cum_Wt
7	B	7895647	1A	10	10
7	B	7895647	2B	10	10
7	B	7895647	2A	10	10
7	B	7895647	2C	10	10
8	B	8763190	3D	20	30
8	B	8763190	3A	20	30
8	B	8763190	4B	20	30
8	B	8763190	5E	20	30
9	C	7641839	1B	25	55
9	C	7641839	4D	25	55
9	C	7641839	3D	25	55
9	C	7641839	1K	25	55
10	D	8566128	2J	15	70
10	D	8566128	2P	15	70
10	D	8566128	5T	15	70
10	D	8566128	8U	15	70

Re: Calculate Distinct value of Rangesum

sunny_talwar — Thu, 10 Oct 2019 15:00:36 GMT

Table: LOAD * INLINE [ Time, Zone, Order, SKU, Weight 7, B, 7895647, 1A, 10 7, B, 7895647, 2B, 10 7, B, 7895647, 2A, 10 7, B, 7895647, 2C, 10 8, B, 8763190, 3D, 20 8, B, 8763190, 3A, 20 8, B, 8763190, 4B, 20 8, B, 8763190, 5E, 20 9, C, 7641839, 1B, 25 9, C, 7641839, 4D, 25 9, C, 7641839, 3D, 25 9, C, 7641839, 1K, 25 10, D, 8566128, 2J, 15 10, D, 8566128, 2P, 15 10, D, 8566128, 5T, 15 10, D, 8566128, 8U, 15 ]; FinalTable: LOAD *, If(Time = Previous(Time) and Zone = Previous(Zone), Peek('Cum_Wt'), RangeSum(Peek('Cum_Wt'), Weight)) as Cum_Wt Resident Table Order By Time, Zone; DROP Table Table;