Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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_YearC_QtrProductCustomer NumberMember TypeQuantity (Sum of Quarter)Discount_TypeTierDiscount%
20144Apple10001AAA80GG_Tier_33
20144Orange10001AAA150GG_Tier_33
20144Banana10001AAA110GG_Tier_33
20144Apple10002BBB120GG_Tier_33
20144Orange10002BBB88GG_Tier_33
20144Banana10002BBB126GG_Tier_33
20144Apple10003CCC98GG_Tier_33

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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)

View solution in original post

11 Replies
robert_mika
Master III
Master III

Can you give desired result for  green columns?

maxgro
MVP
MVP

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;

Not applicable
Author

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.

Not applicable
Author

Massimo,

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

robert_mika
Master III
Master III

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

Not applicable
Author

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.

Not applicable
Author

Mika,

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

Not applicable
Author

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

Not applicable
Author

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