sum 2 fields in separate rows

meadow_ryan

Hello,

 

I have a field 'miles' where for a loaded movement the miles is one value and for an unloaded movement the miles is another value. I need to sum these two together to get the total miles, but both rows of data need to show?

BlueRooster

Is it possible for you to provide a small example of the data? I'm unclear as to how many columns are actually in play for this issue.

meadow_ryan

https://meadowlarkco.domo.com/page/1446302714/kpis/details/2099641706

 

 

Trying to sum 1032 + 40

Chips

That is a link to your instance that only users from your instance will be able to access. 

 

Can you upload a screenshot?

meadow_ryan

image is attached....thanks

Chips

@meadow_ryan you want all 4 rows to show, and all have "1,072" as the miles? 

meadow_ryan

ideally only on the rows with loaded/empty = 'L'

Chips

You are going to have to do that in Magic ETL. 

 

Input Data Set (Current Data Set you are using)

Select Columns 1. Order Number (Rename to "Order Number LE") 2. Loaded/Empty 3. Miles

Remove Duplicates Using All Three Columns 1.Order Number LE  2. Loaded/Empty 3. Miles

Group By 1.Order Number LE  Column AND 2. Loaded/Empty Column, Aggregate Sum 3. Miles to a New Column Called "Total Miles" 

Select Columns 1.Order Number LE  2. Total Miles

Remove Duplicates 1. Order Number LE 2. Total Miles

 

Join (Left Outer) Original Data Set (Using "Order Number" column) WITH The Output from what you just did (Using "Order Number LE" column)

 

Select Columns "Add All", and then remove "Order Number LE"

 

Output Dataset (you can choose the name)

 

And with that, you should have your original dataset with a new column called "Total Miles" that you can use in place of Miles. I would start a new, test card, to make sure everything is working.

 

Let me know how it goes!

meadow_ryan

the Loaded/Empty and Miles are not in the same table as the Order Number is

 

Order Number is from Orders table

 

Loaded/Empty and Miles are from Movement table

 

they are joined by the order from movement_order innner join with order from orders

Chips

Even better! 

 

Sounds like then you could do the Loaded/Empty Mile aggregation (Create a "Total Miles" column via Group By) in the Movement table and then just bring "Total Miles" through into your existing dataset.

 

Give it a shot, let me know how it goes!