Need help in computing Discounts based on Min and Max limits (by Customer)

[#1]

We have two data sets. 1. Transactions Data 2. Discount Tiers with Min and Max values

Common fields between two data sets are…

• C_Year/C_Qtr/Product/Member Type

Based on Customer Number,member type and purchased Quantity (from Transaction Data), we need to comare “Total Purchased Quantity” between MIN and MAX limits in 'Discount Tiers' and extract ‘Tier’ and ‘Discount %’ (from Discount table- 2nd sheet in excel)

Excel with ‘Transaction Data’ and ‘Discount Tiers’ has enclosed (two separate sheets)

Desired output is below….(Yellow form Tran Data and Green from Discount Tiers)

C_Year	C_Qtr	Product	Customer Number	Member Type	Quantity (Sum of Quarter)	Discount_Type	Tier	Discount%
2014	4	Apple	10001	AAA	80	G	G_Tier_3	3
2014	4	Orange	10001	AAA	150	G	G_Tier_3	3
2014	4	Banana	10001	AAA	110	G	G_Tier_3	3
2014	4	Apple	10002	BBB	120	G	G_Tier_3	3
2014	4	Orange	10002	BBB	88	G	G_Tier_3	3
2014	4	Banana	10002	BBB	126	G	G_Tier_3	3
2014	4	Apple	10003	CCC	98	G	G_Tier_3	3

I updated previous thread with more clear data and with clear desired output.

(We have 100 Products and 1000+ customers sales data in our complete data set)

Really appreciate for all your help.

[maxgro · Accepted solution]

The problem I have with your data is for every (Transaction Data)  C_Year, C_Qtr, Product, Member_Type

there are 2 matching (range) discount type (G V) in Discount Tires

So when i filter 1 discount type the result seems ok but without filter no (see attachment)

[#3]

you are awesome!!! Thank you so much for your help. One quick question...can I left join multiple Discount tables to my Transaction table via Intervalmatch? please clarify.