Sum multiple values for the same Invoices

zuchu
zuchu Member
in Charting

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 😊

Best Answer

  • zuchu
    zuchu Member
    Answer ✓

    @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

Answers

  • Byboth
    Byboth Contributor

    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
    zuchu Member
    Answer ✓

    @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
    McSQL Contributor

    @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


    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories