Mapping price discount based on product category

[valpassos]

Hi community,

I'm in a retail project. I have merchandise (products) to which I need to apply a volume discount, based on the category of the product.
I have a ProductCategory table, like the following:

ProductCategory:
- ID_Category (100, 101, 102,...)
- Name
- Description
- Classification
- DiscountType (this is the new field I introduced that groups me the products based on family to which apply the volume discount). It takes the values 1 or 2. 

In my Volume_Discount_2018 file I have the following fields:

Volume_Discount_2018:
- ID_Client (Id of the client that sold that category of product)
- ID_ProductCategory (links to the ID_Category of the ProductCategory table)
- Year
- Discount (actual value of the volume discount)

I need to implement this rule:
- If there exists, in the Volume_Discount_2018, a client with the ID_ProductCategory = 000, then the volume discount should be applied to all products sold by that client, regardless of category.
- If not, then we have a category-based discount (1 or 2), depending on what we have in the DiscountType column.

I'm having trouble implementing this rule in my facts table. I've already prepared the ProductCategory to partiton my category groups with the field DiscountType, that takes the value 1 or 2.
Now, how should I link this DiscountType with the info coming from Volume_Discount_2018 table? And what ApplyMap should I do in my facts table? I'm missing the final step!

Thanks in advance, folks!

Lisa

[Zhandos_Shotan]

Hi!

If i understand correctly, you need to add this steps:

ClientsWithPC000:  // table of clients that sold prod cat 000
LOAD
ID_Client,
ID_ProductCategory
RESIDENT
Volume_Discount_2018
WHERE ID_ProductCategory = '000';

JOIN(Volume_Discount_2018) LOAD   // add field by client ID 
ID_Client,
'1' AS ApplyDiscount // mark clients to apply a volume discount as 1
RESIDENT ClientsWithPC000;

DROP TABLE ClientsWithPC000;

and finally Load and check ApplyDiscount  condition

Regards

[valpassos]

Hi @Zhandos_Shotan!

Thanks for your help, but I'm not exactly getting the purpose of the Join...

How are you dealing with the discount types 1 and 2?

I currently have these mappings in my dimensions:

Map_ClientProdToVolumeDiscount:
MAPPING
LOAD
ID_Client & ID_ProductCategory & Year,
Discount
Resident Volume_Discount_2018;

Map_ProdCategoryToDiscountType:
MAPPING
LOAD
ID_ProductCategory,
DiscountType
RESIDENT ProductCategory;

The problem now is, what to do with these mappings in my factual table? How do I incorporate the codes (000, 1 or 2) logic there?

Thanks!

Lisa

[Zhandos_Shotan]

Join is for adding additional field to indicate Clients who have to have Volume discount.
If your volume discount code is '999', then redefine DiscountType by condition:

If(ApplyDiscount =' 1', '999', DiscountType) as DiscountType

[valpassos]

Where are you defining that condition, @Zhandos_Shotan ?

If(ApplyDiscount ='1', '000', DiscountType) as DiscountType

In the ProductCategory table, Volume_Discount_2018 table or fact table (my sales data)?  

I'm having problem understanding the relationship.

In my Volume_Discount 2018 table, I have the field ID_ProductCategory that takes the values: 000, 101, 102, 103,... where 000 takes a special meaning, and the others are the typical identifier for each Product Category, which I need to convert to 1 or 2 based on the ProductCategory table (for example, if ID_Category = 101, then DiscountType = 1... I have the mapping in order that gives me this).

The problem now is to actually implement the rule in the fact table! This rule:

IF there exists a line in my Volume_Discount_2018 table with Client ID & '000' & Year, then direct discount,
ELSE, grab the discount associated with 1 or 2 DiscountType

Am I making myself clear?

Thanks!

[valpassos · Accepted solution]

Forgot to update, but... Solved!

I just had to do a proper ApplyMap() in my fact table.