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.

[robert_mika]

Can you give desired result for  green columns?

[maxgro]

maybe with IntervalMatch, here for detail

IntervalMatch

Discount:

LOAD C_Year, C_Qtr, Product, Discount, Member_Type, Tier, Low_Limit, High_Limit, Discount%

FROM [For Qlik.xlsx] (ooxml, embedded labels, table is [Discount Tiers]);

Transaction:

LOAD C_Year, C_Qtr, Product, [Customer Number], Quantity, [Member Type] as Member_Type

FROM [For Qlik.xlsx] (ooxml, embedded labels, table is [Transaction Data]);

left join IntervalMatch(Quantity, C_Year, C_Qtr, Product, Member_Type)

LOAD  Low_Limit, High_Limit, C_Year, C_Qtr, Product, Member_Type //, Tier, Discount%

Resident Discount;

[#4]

Robert and Group,

I added real life data (as a sample). My Excel has 4 tabs...

• Transaction Data
• Discount Tiers (Master Data)
• Summary (I made summary to under stand more clear)
• Desired Result (The output our business would like to see)

Thank you all for your help.

[#5]

Massimo,

I updated my Excel and desired output. Now the concept is more clear. Can you help us please?

[robert_mika]

It would be good if your Excel file has exactly the same data that your online table

We got names of Product here and just Product1,2,3... in your Excel file...

[#7]

Could you not join the tables in the script based on the conditions you describe?

Equijoins on the fields that need to be an exact match and then < > below values as appropriate joining the sales to the discount group?

Assuming that the data is available in SQL which I appreciate isn't necessarily the case.

[#8]

Mika,

The latest table I have (in Excel) updated data. Please take a look.

[#9]

Yes. The data is SQL server. But Data is coming from two different severs. (Discount Data from one server and Transaction data from other server).

[#10]

I'd be all over that with Integration Services, but I'm sure someone will come along with a purely Qlik based solution.