Sum multiple values for the same Invoices

zuchu · 2022-11-01T18:38:39+00:00

There was an error rendering this rich post.

Hello,

I have a dataset that contains multiple rows of orders. I got an account number that represents the same client but the same client can have multiple orders. I would like to create a transformation that would SUM revenues and expenses that are all assigned to the same account number that represent 1 unique customer. In other words I want to sum up some values in different rows based on the same Account number. (I could do that from analyzer but I would really like to know how to create such transformation on the ETL level.

Thanks 😊

Find more posts tagged with

Accepted answers

zuchu

@Byboth can you explain? What formula would you use? I was thinking to sum those values up based on the docnum but formula editor does not support group by function

All comments

Byboth

I would use a formula tile in magic ETL. You would also need to group by the account number. If you want the same number on each row, so you still have multiple rows I would branch this logic out and then join itself back.

zuchu

@Byboth can you explain? What formula would you use? I was thinking to sum those values up based on the docnum but formula editor does not support group by function

McSQL

@zuchu I think this will cover what you want to do. If not, can you provide an example screenshot (maybe in excel) of what the input looks like and output.

You can use the group by tile. In the "Select what columns identify the grouping" you would choose the account number.

In the new aggregated columns, you would choose the correct columns in your data set and choose the aggregation type of "Sum".

Screen Shot 2022-11-01 at 3.01.35 PM.png