special cumulative sum

jolio007 — Tue, 30 Apr 2019 14:18:00 GMT

What I start with is quantity sold of an item per period. Periode 2 is a cumul of period 1. Sometimes the item change providor period 2. My goal is to find the amount sold each period per item per provider.

Here's an example

ref	provider	ref//provider	Periode 1	Quantity1	Periode 2	Quantity2
1	247	1//247		10		10
1	555	1//555		0		23
3	555	3//555		15		15
3	88	3//88		0		32
5	89	5//89		5		10
5	90	5//90		0		0
8	91	8//91		20		40
8	92	8//92		0		0
24	93	24//93		10		10
24	94	24//94		0		20
24	95	24//95		0		0
26	96	26//96		-100		-100
26	97	26//97		0		50
28	98	28//98		-120		-120
28	99	28//99		0		-100
29	100	29/100		10		45
30	5	30//5		0		0
31	6	31//6		-1546		100
32	7	32//7		-500		-500
32	8	32//8		0		250
32	9	32//9		0		0
32	10	32//10		0		0

The problem with this is that if I do a sum of the column quantity2, I don't get the real amount sold during that period. I get the real value + the cumul of the same ref sold previous periods

Ideally, I'd want to get this

ref	fourn	ref//fourn	T1	Qte	T2	Qte
1	247	1//247		10		10
1	555	1//555		0		13
3	555	3//555		15		15
3	88	3//88		0		17
5	89	5//89		5		10
5	90	5//90		0		0
8	91	8//91		20		40
8	92	8//92		0		0
24	93	24//93		10		10
24	94	24//94		0		10
24	95	24//95		0		0
26	96	26//96		-100		-100
26	97	26//97		0		150
28	98	28//98		-120		-120
28	99	28//99		0		20
29	100	29/100		10		45
30	5	30//5		0		0
31	6	31//6		-1546		100
32	7	32//7		-500		-500
32	8	32//8		0		750
32	9	32//9		0		0
32	10	32//10		0		0

I get the real amount of things sold per ref//fourn. Each period.

I've been able to this by hand but using talend, I find it quite challenging. At first, I looked at the biggest number in quantity grouped by ref using an taggregateRow. Then from those I'd remove the other quantity in the same grouped by ref. It worked well for positive numbers but badly for negative number. Especially those with 0 as quantity and minuses

Thank you so much for your help

topic Re: special cumulative sum in Talend Studio

special cumulative sum

Re: special cumulative sum