ETL Calculation

zuchu
zuchu Member
in Beast Mode

Hello,


I have SO dataset and want to create a calculated field that would show the sum of the items within an order. To give an example, I have an order that has 5 rows of information (5 different items), I want to create another field that would sum up the cost of the items and create another field that would show the same value for those rows? ex. items cost 2,2,3,3,4. I want to have another field that would show that based on the order number, the sum is 14. Any ideas what transformations I need to create?

Answers

  • RobSomers
    RobSomers Coach

    @zuchu You will want to use a Rand & Window tile:

    https://domo-support.domo.com/s/detail/ka05w00000122yVAAQ?language=en_US

    You'll do a sum of the costs and partition by the order number. This will give you the total of the order on each row that the order number appears.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • zuchu
    zuchu Member

    @RobSomers Can you walk me through the process?


    I created a new column name, then applied sum as a function based on the COST column, then not sure what to put as a frame range to be used??

    Later I assume column to order on functions on is my order number, ascending/descending and should i do anything with 'what columns make up the partition'?

  • ST_-Superman-_
    ST_-Superman-_ Coach

    My approach would be to use a group by tile here. I would group your orders by whatever your unique identifier is for a single order (the one with 5 items in this case). I'll call this field OrderID for demonstration purposes.

    You would use a group by tile and group the data by OrderID . You would then add an aggregate field that would be the SUM of Cost field. You could call this field TotalCost

    Next, you join this back to your original data (join on OrderID ). This will add a column to your original table that would be called TotalCost and would be the sum of all Cost within a single OrderID


    @RobSomers I would recommend using rank and window tiles when you need a running total.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories