calculate sum by transaction number and order number

[Jocelynupup]

Hi team,

I am currently working on the workflow. The rule is to sum the price for the same transaction number and for the same order number. Negative prices indicate a return. But I just realize there is a trick. A transaction number might have multiple order numbers. And an order number might have multiple transaction numbers. When we do the sum, we cannot sum the duplicate part (8051.8). Do you have any suggestions on how to do this?

The data is below. 

transaction number	order num	amt
5691	130379	8051.8
5749	130379	-162.09
5691	130380	8051.8
5750	130380	165.14
5752	130380	24.27
5856	130380	-165.14
5691	130378	8051.8
5714	130378	-359.12
5751	130378	-24.27
7805	113991	68.05
7811	113991	204.16
0222	176548	3183.2
0222	176547	3183.2
0040	150636	5013

The result is below.

8051.8+-162.09+165.14+24.27+-165.14+-359.12-24.27=7530.56

transaction number	order num	amt	total order amt
5691	130379	8051.8	7530.56
5749	130379	-162.09	7530.56
5691	130380	8051.8	7530.56
5750	130380	165.14	7530.56
5752	130380	24.27	7530.56
5856	130380	-165.14	7530.56
5691	130378	8051.8	7530.56
5714	130378	-359.12	7530.56
5751	130378	-24.27	7530.56
7805	113991	68.05	272.21
7811	113991	204.16	272.21
0222	176548	3183.2	3183.23
0222	176547	3183.2	3183.23
0040	150636	5013	5012.97

[alexnajm]

Use a Summarize or a Unique to remove the duplicates, and then Summarize to get the total - then you can Append it back to the original!

[Jocelynupup]

Sorry, my data is not completed. I expanded my original data. The data need to be grouped by transaction number and order number.

[alexnajm]

There is no way to know which transaction goes to which group based on the data provided - for example, what column can be used to know which group is which? It cannot be transaction number

transaction number	order num	amt	total order amt
5691	130379	8051.8	7530.56
5749	130379	-162.09	7530.56
5691	130380	8051.8	7530.56
5750	130380	165.14	7530.56
5752	130380	24.27	7530.56
5856	130380	-165.14	7530.56
5691	130378	8051.8	7530.56
5714	130378	-359.12	7530.56
5751	130378	-24.27	7530.56
7805	313991	68.05	272.21
7811	113991	204.16	272.21
0222	176548	3183.2	3183.23
0222	176547	3183.2	3183.23
0040	150636	5013	5012.97

[CoG · Accepted solution]

I do believe this can be done, assuming I understood the problem correctly! Check out this video for details on how the solution works:

https://youtu.be/rP_381tztzs

Hope this helps and Happy Solving!

[Jocelynupup]

Hi CoG,

Do you have any ideas why the following dataset doesn't work?

POS                         ORDER
1012405606561790 SB012497606568166
1012406606562560 RB012497606568165
1012406606562560 SB012497606568166
1012406606562562 SB012497606568166
1031201606569427 RB012497606568165

They should have had the same groupID based on the logic.

Thanks,

Jocelyn