I need some logic for this table please... guru needed!

user07888
user07888 Member
in Archive

I have a table with 3 fields in it:

 

Order ID

Gift Name

Gift Quantity

 

an Order ID can be an order for several gifts, or just 1. So you might have a table like

 

Gb121   Gift Name 1    1

GB121   Gift Name 2    3

GB122   Gift Name 7    1

 

What I need is a logic statement that allows me the only display table items where the TOTAL number of gifts, per Order ID, is above x

 

Happy if that involves creating a beast mode extra field for example, I just don't know how to write the logic for the function.

 

Can anyone help?

 

Thanks

 

Stuart

 

Comments

  • Valiant
    Valiant Coach

    So the way I would approach this would be to find the total # of gifts for each order ID and then add that as it's own column to your data. (The reason for this is that you can't use aggregation functions in beast mode filters)

     

    So to do this I would write a simple MySQL query like this (If you prefer to use the ETL tool you can as well, just follow this logic). 

    SELECT `Order ID`, SUM(`Gift Quantity`) AS 'Total Gift'
    FROM yourtablename 
    GROUP BY `Order ID`

    This will give you the total gift count for each order id.

     

    Now do a left join (on Order ID) with your original dataset on the left side to the above query results. We want the Total Gift value to be repeated each time for the corresponding Order ID.

     

    Now use this final result as your dataset for this card.

     

    Add Total Gift as a filter and now you can set the X value you mentioned to whatever you want and have it filter out the non-matching orders.

     

    Hope this helps, let me know if you have any questions.

    Valiant

  • Property_Ninja
    Property_Ninja Contributor

    Hi Stuart,

     

    I think in this situation you may want to create a new table and then join it to your pre-existing table in a dataflow. 

    -- A transform called total_gift_quantity
    select sum(`Gift Quantity`) as total_quantity
    ,`Order ID`
    from your_table
    group by `Order ID`

    -- A new transform that joins total_gift_quantity to your original table
    select a.*
    ,b.total_quantity
    from your_table a
    left join total_gift_quantity b on a.`Order ID` = b.`Order ID`
    -- This is the table you will now use when charting

    Then you can just filter in your card for when the total_quantity > x and it will only pull records where that condition is true.

     

    Hope this helps,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Categories