Inventory Management: Financial Impact Analysis of Expired Stock with Country-Wise Insights
Requirement
Introduction: Inventory management that calculates the financial impact on expired products involves assessing the losses incurred from products that have passed their expiry dates. Country-wise summary of the financial impact, highlighting which regions are most affected by expired stock. This approach allows businesses to optimize inventory control, reduce waste, and take proactive measures to minimize financial losses on a regional level.
Requirements:
Read the table 'purgo_playground.supply_chain_delivery'.
Calculate the ‘Days_count_For_Expiry' based on expiry date with current date. Categorize inventory based on expiry date in the column Expiry_Risk, when 'Days_Count_For_Expiry' is below zero day then 'Expired', when 'Days_Count_For_Expiry' is less than 90 days then 'High Risk', when 'Days_Count_For_Expiry' is less than 180 days then 'Low Risk', otherwise 'No Risk’. Calculate the financial loss with stock with unit cost. calculate the Country name based financial loss due to expired medicine and calculate overall total loss.
Final Output: Show the results for expired with medicine id, expiry risk, days count for expiry, stock, unit cost with financial loss, provide Country name-wise financial loss and provide overall financial loss filter with expired medicine.
Write the Pyspark code without syntax error.
Unity Catalog: 'purgo_playground.supply_chain_delivery'