topic Re: Need help in computing Discounts based on Min and Max limits (by Customer) in QlikView

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

Sun, 25 Jan 2015 08:38:12 GMT

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.

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

robert_mika — Sun, 25 Jan 2015 12:43:51 GMT

Can you give desired result for green columns?

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

maxgro — Sun, 25 Jan 2015 13:48:04 GMT

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;

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

Sun, 25 Jan 2015 19:56:13 GMT

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.

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

Sun, 25 Jan 2015 20:00:28 GMT

Massimo,

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

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

robert_mika — Mon, 26 Jan 2015 06:24:36 GMT

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

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

Mon, 26 Jan 2015 09:48:58 GMT

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.

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

Mon, 26 Jan 2015 15:25:41 GMT

Mika,

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

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

Mon, 26 Jan 2015 15:28:34 GMT

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

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

Mon, 26 Jan 2015 15:30:52 GMT

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

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

maxgro — Mon, 26 Jan 2015 17:18:11 GMT

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)

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

Tue, 27 Jan 2015 04:50:07 GMT

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.