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

user07888

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

 

BlueRooster

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