ETL Calculation

zuchu

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?

RobSomers

@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.

zuchu

@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

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.