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
