topic Re: Sales and client specific discounts data model in QlikView

Sales and client specific discounts data model

Thu, 17 Apr 2014 07:01:52 GMT

Hello,

I have got two tables

Transactions table:

Date	Client code	Product ID	Quantity	Price	Amount
2014-01-05	UAB253	0101001	10	5.4	54
2014-02-10	UAB253	0101002	15	10	150
2014-03-20	UAB260	0101002	4	4	16

Discounts table:

Client code	DateFrom	DateTo	Discount type	Discount amount
UAB253	2014-01-01	2014-01-31	1	5%
UAB253	2014-01-01	2014-04-30	2	10%
UAB260	2014-01-01	open	3	0.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?

Re: Sales and client specific discounts data model

ariel_klien — Thu, 17 Apr 2014 07:30:34 GMT

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

Re: Sales and client specific discounts data model

Thu, 17 Apr 2014 07:35:49 GMT

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.

Re: Sales and client specific discounts data model

jonathandienst — Thu, 17 Apr 2014 07:58:25 GMT

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

Re: Re: Sales and client specific discounts data model

ariel_klien — Thu, 17 Apr 2014 08:03:01 GMT

Hi,

Please See the Attachmant.

Hope this will help you.

Ariel

Re: Re: Sales and client specific discounts data model

Tue, 22 Apr 2014 06:55:00 GMT

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:

Date	Client code	Product ID	Quantity	Price	AmountD	Discount1	Discount2	Discount3	Discount4	Discount5	DiscountX

Can You help me on this?

Re: Re: Re: Sales and client specific discounts data model

Tue, 22 Apr 2014 08:51:31 GMT

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