sum 2 fields in separate rows

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?

Comments

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

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

     

    Can you upload a screenshot?

  • image is attached....thanks

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

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

  • 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!

  • 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

  • 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!

This discussion has been closed.