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

Sales and client specific discounts data model

Hello,

I have got two tables

Transactions table:

DateClient codeProduct IDQuantityPriceAmount
2014-01-05UAB2530101001105.454
2014-02-10UAB25301010021510150
2014-03-20UAB26001010024416

Discounts table:

Client codeDateFromDateToDiscount typeDiscount amount
UAB2532014-01-012014-01-3115%
UAB2532014-01-012014-04-30210%
UAB2602014-01-01open30.5 $/quantity

I want to obtain a real price for each transaction. Discounts are only client specific.

How to I go about creating a correct data model?

1 Solution

Accepted Solutions
ariel_klien
Specialist
Specialist

Hi,

Please See the Attachmant.

Hope this will help you.

BR

Ariel

View solution in original post

6 Replies
ariel_klien
Specialist
Specialist

Hi,

What happened when you have 2 discounts in the same period of time for the same customer?

Line 1 in the Transactions table and line 1 & 2 in the Discounts table.

Ariel

Not applicable
Author

If two or more discounts apply for the same date, then they must be aggregated. In this case it would be 5%+10% = 15%. However, type 3 discount is absolute number and it must be applied after percentile discounts were applied.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

This is a case for the slowly changing dimension (SCD) version of interval match:

Transactions:                                                  // load fields as per your post

LOAD .....

Discounts:                                                       // load fields as per your post, but...

LOAD .....

     If (DateTo = 'open', null(), DateTo),        // 'open' will be achieved with a null date to

     ....

Left Join (Transactions)

IntervalMatch(Date, ClientCode)

LOAD DateFrom,

     DateTo,

     ClientCode

Resident Discounts;

// --- Optional code to bring the discount amounts into the transaction table:

Left Join (Transactions)

LOAD * Discounts;

Drop Table Discounts;                  // table not required any more

Drop Fields DateFrom, DateTo   // optional field cleanup

// --- End optional code

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ariel_klien
Specialist
Specialist

Hi,

Please See the Attachmant.

Hope this will help you.

BR

Ariel

Not applicable
Author

Thanks, Jonathan,

however, your script returns me transactions table with increased amount of lines. It duplicates or triplicates number of transaction lines depending on how many discounts apply for the same date.

While Ariel's script was more helpful, it returns sumed values for discounts. I would like to return a transactions table in a form like this:

DateClient codeProduct IDQuantityPriceAmountDDiscount1Discount2Discount3Discount4Discount5DiscountX

Can You help me on this?

Not applicable
Author

Actually I used Ariel's script to solve my problem. Thanks Ariel.