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!
If i understand correctly, you need to add this steps:
ClientsWithPC000: // table of clients that sold prod cat 000
WHERE ID_ProductCategory = '000';
JOIN(Volume_Discount_2018) LOAD // add field by client ID
'1' AS ApplyDiscount // mark clients to apply a volume discount as 1
DROP TABLE ClientsWithPC000;
and finally Load and check ApplyDiscount condition
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:
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?
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?