MSL-HCP Interactions on Sales with Therapeutic area mappings

Introduction: To enhance the MSL-HCP sales interaction reporting pipeline by integrating dynamic therapeutic area mappings through a parameterized lookup table, replacing hardcoded mappings for better scalability, flexibility, and maintainability..

 

Requirements: Create a PySpark logic to read the tables purgo_playground.msl_hcp_data and purgo_playground.therapeutic_area. Perform the therapeutic mapping join on the Product_ID column in both tables. Handle the therapeutic area column based on Product_ID, and if a product is not found in the mapping table, mark the therapeutic area as "Unmapped". To derive KPIs, Add a Sales_Category column, if Sales_Amount > 15000, mark as High; if Sales_Amount > 10000, mark as Medium; otherwise, mark as Low. Add an Interaction_Quarter column with values Q1, Q2, Q3, Q4 based on the interaction date.

 

Final Output: Show the result with column of "MSL_ID", "HCP_ID", "Product_ID", "Product_Name", "Therapeutic_Area", "Sales_Amount", "Region", "Interaction_Quarter"

 

Unity Catalog: ‘purgo_playground.msl_hcp_data', ‘purgo_playground.therapeutic_area’