topic Re: Mapping price discount based on product category in App Development

Mapping price discount based on product category

valpassos — Sat, 16 Nov 2024 06:18:20 GMT

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

Re: Mapping price discount based on product category

Zhandos_Shotan — Tue, 19 Mar 2019 04:34:55 GMT

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

Re: Mapping price discount based on product category

valpassos — Tue, 19 Mar 2019 14:44:43 GMT

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

Re: Mapping price discount based on product category

Zhandos_Shotan — Tue, 19 Mar 2019 12:14:59 GMT

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

Re: Mapping price discount based on product category

valpassos — Tue, 19 Mar 2019 14:43:56 GMT

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!

Re: Mapping price discount based on product category

valpassos — Thu, 09 May 2019 13:04:27 GMT

Forgot to update, but... Solved!

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