# Databricks notebook source ### constant DATA_TYPE_MARKET = 'MARKET' DATA_TYPE_MOLECULE = 'MOLECULE' DATA_TYPE_BRAND = 'BRAND' DATA_TYPE_MULTI_MARKET = 'MULTI_MARKET' DATA_TYPE_MULTI_MOLECULE = 'MULTI_MOLECULE' DATA_TYPE_MULTI_BRAND = 'MULTI_BRAND' DATA_TYPE_DUPLICATE = 'DUPLICATE' DATA_TYPE_HEDGE_TA = 'HEDGE_TA' DATA_TYPE_HEDGE_MARKET = 'HEDGE_MARKET' DATA_TYPE_HEDGE_MOLECULE = 'HEDGE_MOLECULE' DATA_TYPE_HEDGE_BRAND = 'HEDGE_BRAND' DATA_FLAG_RAW = 0 ### final pack_flag = 1 DATA_FLAG_SUB = 1 ### final pack_flag = 2 DATA_FLAG_ADD = 2 ### final pack_flag = 2 DATA_FLAG_RATIO = 3 ### final pack_flag = 2 DATA_FLAG_AVG = 4 ### final pack_flag = 2 DATA_FLAG_HEDGE = 5 ### final pack_flag = 2 BRAND_FLAG_NATIVE = 1 ### final brand_flag = 1 BRAND_FLAG_CALCULATE = 2 ### final brand_flag = 2 BRAND_FLAG_NONE = 3 ### final brand_flag = 2 BRAND_FLAG_PACK = 4 ### final brand_flag = 1 DATA_OTHER_TA = 'OTHER_TA' DATA_OTHER_MARKET = 'OTHER_MARKET' DATA_OTHER_MOLECULE = 'OTHER_MOLECULE' DATA_OTHER_BRAND = 'OTHER_BRAND' DATA_AREA_TYPE_ROC = 'ROC' DATA_LABEL_AREA_ALL = '全国' # COMMAND ---------- ############################################################START############################################################## ### STEP-1: generate product_id&level relationship mapping table ### STEP-2: split regional data by brand ratio when there is no regional data and there is a brand ratio ### STEP-3: substract ROC data # COMMAND ---------- # MAGIC %md # MAGIC ## STEP-1: generate product_id&level relationship mapping table # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.1 generate mapping table # MAGIC -- use two dwd table: # MAGIC -- use dwd.dwd_gnd_ext_retail_pack_property to find the relationship with product_id & brand # MAGIC -- use dwd.dwd_inc_gnd_retail_b2c_label_total to find the brand with suffix label ,such as 胶囊、颗粒 (可威-胶囊/可威-颗粒) # MAGIC ------------------------------------------------------------------------------------- # MAGIC # MAGIC with tmp_pack_property as ( # MAGIC --------------------------------------------------------------- # MAGIC -- 以数字开头的iqvia_pack_code调整为12位（以字母开头的不变），不足位数补0 # MAGIC -- iqvia_prod_code调整为9位，不足位数补0 # MAGIC select # MAGIC case when length(trim(iqvia_pack_code)) < 12 and trim(iqvia_pack_code) REGEXP '^[0-9]' then right(concat('000000000000',trim(iqvia_pack_code)),12) else trim(iqvia_pack_code) end as iqvia_pack_code, # MAGIC case when length(iqvia_prod_code) < 9 then right(concat('000000000',iqvia_prod_code),9) else iqvia_prod_code end as iqvia_prod_code, # MAGIC product_id, # MAGIC cmps_des, # MAGIC cmps_des_c, # MAGIC prod_des, # MAGIC prod_des_c, # MAGIC corp_cod, # MAGIC app1_cod, # MAGIC app2_cod, # MAGIC app3_cod # MAGIC from dwd.dwd_gnd_ext_retail_pack_property # MAGIC ), tmp_b2c_label_total as ( # MAGIC select # MAGIC case when length(IQVIA_PROD_CODE) < 9 then right(concat('000000000',IQVIA_PROD_CODE),9) else IQVIA_PROD_CODE end as IQVIA_PROD_CODE, # MAGIC ZK_Prod_C, # MAGIC APP1_COD, # MAGIC APP2_COD, # MAGIC APP3_COD # MAGIC from dwd.dwd_inc_gnd_retail_b2c_label_total # MAGIC --------------------------------------------------------------- # MAGIC ), tmp_suffix_brand as ( # MAGIC select distinct split(brand,'-')[0] # MAGIC from dwd.dwd_gnd_retail_split_automatic # MAGIC where brand like '%-%' # MAGIC ), tmp_mapping as ( # MAGIC select # MAGIC distinct # MAGIC a.product_id as product_id, # MAGIC a.iqvia_pack_code as pack_code, # MAGIC a.cmps_des as molecule_code, # MAGIC a.cmps_des_c as molecule_name, # MAGIC ifnull(a.prod_des, concat(a.cmps_des,'_OTHER_PROD')) as product_code, # MAGIC ifnull(a.prod_des_c, concat(a.cmps_des_c,'_OTHER_PROD')) as product_name, # MAGIC a.corp_cod # MAGIC from tmp_pack_property a # MAGIC where a.prod_des_c not in (select * from tmp_suffix_brand) # MAGIC or a.prod_des_c is null # MAGIC # MAGIC union all # MAGIC # MAGIC select # MAGIC distinct # MAGIC a.product_id as product_id, # MAGIC a.iqvia_pack_code as pack_code, # MAGIC a.cmps_des as molecule_code, # MAGIC a.cmps_des_c as molecule_name, # MAGIC a.prod_des as product_code, # MAGIC ifnull(b.ZK_Prod_C, a.prod_des_c) as product_name, # MAGIC a.corp_cod # MAGIC from tmp_pack_property a # MAGIC left join tmp_b2c_label_total b # MAGIC on a.iqvia_prod_code = b.IQVIA_PROD_CODE # MAGIC and a.app1_cod = b.APP1_COD # MAGIC and a.app2_cod = b.APP2_COD # MAGIC and a.app3_cod = b.APP3_COD # MAGIC where a.prod_des_c in (select * from tmp_suffix_brand) # MAGIC ), tmp_pack as ( # MAGIC select # MAGIC distinct # MAGIC product_id, # MAGIC zk_product_id, # MAGIC level_ta, # MAGIC level_market # MAGIC from tmp.tmp_retail_pack_rawdata # MAGIC ), tmp_pack_mapping as( # MAGIC select # MAGIC p.level_ta, # MAGIC p.level_market, # MAGIC p.product_id, # MAGIC p.zk_product_id, # MAGIC m.pack_code, # MAGIC m.molecule_code, # MAGIC m.molecule_name, # MAGIC m.product_code, # MAGIC m.product_name, # MAGIC m.corp_cod # MAGIC from tmp_pack p # MAGIC inner join tmp_mapping m # MAGIC on p.product_id = m.product_id # MAGIC ), tmp_level as ( # MAGIC select # MAGIC distinct # MAGIC ta, # MAGIC market, # MAGIC molecule, # MAGIC brand # MAGIC from tmp.tmp_retail_level_brand # MAGIC order by ta,market,molecule,brand # MAGIC ), tmp_molecule_brand as ( # MAGIC -----------------------ta matched & market matched & molecule matched & brand matched -------------- # MAGIC select # MAGIC a.*,b.*,1 as brand_flag # MAGIC from tmp_pack_mapping a # MAGIC inner join tmp_level b # MAGIC on a.level_ta = b.ta # MAGIC and a.level_market = b.market # MAGIC and (a.molecule_name = b.molecule or b.molecule <> 'OTHER_MOLECULE') -- 新增自定义非OTHER_MOLECULE # MAGIC and a.product_name = b.brand # MAGIC ), tmp_other_brand as ( # MAGIC -----------------------ta matched & market matched & OTHER_MOLECULE & brand matched----------------- # MAGIC select # MAGIC a.*,b.*,1 as brand_flag # MAGIC from tmp_pack_mapping a # MAGIC inner join tmp_level b # MAGIC on a.level_ta = b.ta # MAGIC and a.level_market = b.market # MAGIC and b.molecule = 'OTHER_MOLECULE' # MAGIC and a.product_name = b.brand # MAGIC where a.product_id not in ( # MAGIC select distinct product_id from tmp_molecule_brand # MAGIC ) # MAGIC ), tmp_molecule_other as( # MAGIC -----------------------ta matched & market matched & molecule matched & OTHER_BRAND---------------- # MAGIC select # MAGIC a.*,b.*,2 as brand_flag # MAGIC from tmp_pack_mapping a # MAGIC inner join tmp_level b # MAGIC on a.level_ta = b.ta # MAGIC and a.level_market = b.market # MAGIC and a.molecule_name = b.molecule # MAGIC and b.brand = 'OTHER_BRAND' # MAGIC where a.product_id not in ( # MAGIC select distinct product_id from tmp_molecule_brand # MAGIC union all # MAGIC select distinct product_id from tmp_other_brand # MAGIC ) # MAGIC ), tmp_other_other as ( # MAGIC -----------------------ta matched & market matched & OTHER_MOLECULE& OTHER_BRAND--------------------- # MAGIC select # MAGIC a.*,b.*,2 as brand_flag # MAGIC from tmp_pack_mapping a # MAGIC inner join tmp_level b # MAGIC on a.level_ta = b.ta # MAGIC and a.level_market = b.market # MAGIC and b.molecule = 'OTHER_MOLECULE' # MAGIC and b.brand = 'OTHER_BRAND' # MAGIC where a.product_id not in ( # MAGIC select distinct product_id from tmp_molecule_brand # MAGIC union all # MAGIC select distinct product_id from tmp_other_brand # MAGIC union all # MAGIC select distinct product_id from tmp_molecule_other # MAGIC ) # MAGIC ), tmp_brand_mapping as ( # MAGIC select * from tmp_molecule_brand # MAGIC union all # MAGIC select * from tmp_other_brand # MAGIC union all # MAGIC select * from tmp_molecule_other # MAGIC union all # MAGIC select * from tmp_other_other # MAGIC ) # MAGIC # MAGIC ------------------------- generate product_id&level relationship mapping table------------------------ # MAGIC insert overwrite table tmp.tmp_retail_pack_mapping # MAGIC # MAGIC select # MAGIC product_id, # MAGIC zk_product_id, # MAGIC level_ta, # MAGIC level_market, # MAGIC molecule as level_molecule, # MAGIC brand as level_brand, # MAGIC pack_code, # MAGIC molecule_code, # MAGIC molecule_name, # MAGIC product_code, # MAGIC product_name, # MAGIC brand_flag, # MAGIC corp_cod # MAGIC from tmp_brand_mapping # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.1 generate mapping table # MAGIC -- 1.1.2 update special case 'GI-慢性胃炎、胃溃疡-其他拉唑类' # MAGIC ------------------------------------------------------------------------------------- # MAGIC UPDATE tmp.tmp_retail_pack_mapping # MAGIC SET level_molecule = '其他拉唑类' # MAGIC where level_ta = 'GI' # MAGIC and level_market = '慢性胃炎、胃溃疡' # MAGIC and level_molecule = 'OTHER_MOLECULE' # MAGIC and level_brand = 'OTHER_BRAND' # MAGIC and molecule_desc like '%拉唑%' # MAGIC --and product_id in ( # MAGIC -- select product_id # MAGIC -- from dwd.dwd_gnd_ext_retail_pack_property # MAGIC -- where cmps_cod IN ('001658','002782','008617','001051','001853','008467') # MAGIC --) # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.1 generate mapping table # MAGIC -- 1.1.3 获取market信息，产品重新归属 # MAGIC -- 1.1.3.1 B2C标签表整合关联tblmarket 表，获取market 信息, 更新tmp.tmp_zk_retail_pack_property # MAGIC ------------------------------------------------------------------------------------- # MAGIC insert overwrite table tmp.tmp_zk_retail_pack_property # MAGIC with tmp_pack_property as ( # MAGIC select # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code & prod_code # MAGIC case when length(trim(iqvia_pack_code)) < 12 and trim(iqvia_pack_code) REGEXP '^[0-9]' # MAGIC then right(concat('000000000000',trim(iqvia_pack_code)),12) # MAGIC else trim(iqvia_pack_code) # MAGIC end as new_iqvia_pack_code, # MAGIC case when length(iqvia_prod_code) < 9 then right(concat('000000000',iqvia_prod_code),9) else iqvia_prod_code end as new_iqvia_prod_code, # MAGIC ------------------------------------------------------------------- # MAGIC * # MAGIC from dwd.dwd_gnd_ext_retail_pack_property # MAGIC ), tmp_pack_additional_attribute as ( # MAGIC select # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code # MAGIC case when length(trim(Pack_Code)) < 12 and trim(Pack_Code) REGEXP '^[0-9]' # MAGIC then right(concat('000000000000',trim(Pack_Code)),12) # MAGIC else trim(Pack_Code) # MAGIC end as new_pack_code, # MAGIC ------------------------------------------------------------------- # MAGIC * # MAGIC from dwd.dwd_ims_td_pack_additional_attribute # MAGIC ), tmp_tblmarket as ( # MAGIC select # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code & prod_code # MAGIC case when length(trim(pack_code)) < 12 and trim(pack_code) REGEXP '^[0-9]' # MAGIC then right(concat('000000000000',trim(pack_code)),12) # MAGIC else trim(pack_code) # MAGIC end as new_pack_code, # MAGIC case when length(Product_Code) < 9 then right(concat('000000000',Product_Code),9) else Product_Code end as new_prod_code, # MAGIC ------------------------------------------------------------------- # MAGIC * # MAGIC from dwd.dwd_gnd_ext_retail_tblmarket # MAGIC ), tmp1 as ( # MAGIC # MAGIC select distinct # MAGIC prod.iqvia_lineno # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code & prod_code # MAGIC ,prod.new_iqvia_pack_code as iqvia_pack_code # MAGIC ,prod.new_iqvia_prod_code as PROD_COD # MAGIC ------------------------------------------------------------------- # MAGIC ,prod.iqvia_notes # MAGIC ,prod.new_pack_flag # MAGIC ,prod.is_exists_chpa # MAGIC ,prod.product_id # MAGIC ,prod.prescription_nature # MAGIC ,prod.medicine_type # MAGIC ,prod.zk_medicine_tier1 # MAGIC ,prod.zk_medicine_tier2 # MAGIC ,prod.zk_medicine_tier3 # MAGIC ,prod.zk_medicine_tier4 # MAGIC ,prod.common_name # MAGIC ,prod.dosage_form # MAGIC ,prod.user_type # MAGIC ,prod.category_name # MAGIC ,prod.product_name # MAGIC ,prod.brand_name # MAGIC ,prod.zk_manu_des # MAGIC ,prod.zk_corp_des # MAGIC ,prod.zk_pack_des # MAGIC ,prod.counting_unit_a # MAGIC ,prod.dosage_unit_a # MAGIC ,prod.unit_a # MAGIC ,prod.app1_cod # MAGIC ,prod.app1_des # MAGIC ,prod.app1_des_c # MAGIC ,prod.app2_cod # MAGIC ,prod.app2_des # MAGIC ,prod.app2_des_c # MAGIC ,prod.app3_cod # MAGIC ,prod.app3_des # MAGIC ,prod.app3_des_c # MAGIC ,prod.atc1_cod # MAGIC ,prod.atc1_des # MAGIC ,prod.atc1_des_c # MAGIC ,prod.atc2_cod # MAGIC ,prod.atc2_des # MAGIC ,prod.atc2_des_c # MAGIC ,prod.atc3_cod # MAGIC ,prod.atc3_des # MAGIC ,prod.atc3_des_c # MAGIC ,prod.atc4_cod # MAGIC ,prod.atc4_des # MAGIC ,prod.atc4_des_c # MAGIC ,prod.bio_desc # MAGIC ,right(concat('000000',nvl(prod.cmps_cod,0 )),6) cmps_cod # MAGIC ,prod.cmps_des # MAGIC ,prod.cmps_des_c # MAGIC ,prod.corp_cod # MAGIC ,prod.corp_des # MAGIC ,prod.corp_des_c # MAGIC ,prod.edl_desc # MAGIC ,prod.eth_otc_desc # MAGIC ,prod.gene_orig_desc # MAGIC ,prod.gqce_desc # MAGIC ,prod.manu_cod # MAGIC ,prod.manu_des # MAGIC ,prod.manu_des_c # MAGIC ,prod.mnfl_cod # MAGIC ,prod.mnfl_des # MAGIC ,prod.nrdl_desc # MAGIC ,prod.pack_des # MAGIC ,prod.stgh_des # MAGIC ,prod.pack_lch # MAGIC ,prod.paed_desc # MAGIC ,prod.prod_des # MAGIC ,prod.prod_des_c # MAGIC ,prod.tcm_desc # MAGIC ,prod.vbp_desc # MAGIC ,prod.unit # MAGIC ,prod.counting_unit # MAGIC ,prod.dosage_unit # MAGIC ,nvl(MOLE.NRDL_Entry_Date,'') AS NRDL_ENTRY_DATE # MAGIC ,tblmkt.market # MAGIC ,tblmkt.bu # MAGIC ,nvl(tblmkt.extend_market_ratio,1) as extend_market_ratio # MAGIC from tmp_pack_property prod # MAGIC LEFT JOIN tmp_pack_additional_attribute MOLE # MAGIC ---------------------------------------------------------------------------------- # MAGIC ON prod.new_iqvia_pack_code = MOLE.new_pack_code # MAGIC ---------------------------------------------------------------------------------- # MAGIC left join ( # MAGIC select market # MAGIC ,bu # MAGIC ,atc1_code # MAGIC ,atc2_code # MAGIC ,atc3_code # MAGIC ,atc4_code # MAGIC ,nfc1_code # MAGIC ,nfc2_code # MAGIC ,nfc3_code # MAGIC ,Molecule_Code # MAGIC ,zk_common_name # MAGIC ,extend_market_ratio # MAGIC ,zk_product_id # MAGIC ,zk_dosage_form # MAGIC ------------------------------------- # MAGIC ,new_pack_code as pack_code # MAGIC ,new_prod_code as Product_Code # MAGIC ------------------------------------- # MAGIC ,not_in_flag # MAGIC ,extend_market # MAGIC from tmp_tblmarket # MAGIC -- where (market not in( 'ZOK Default Market', 'Inhaled Extended Market') or (case when market in( 'Inhaled Extended Market') then zk_product_id is not null end)) # MAGIC where 1=1 # MAGIC and Extend_Market IS NULL # MAGIC AND ( NOT_IN_FLAG IS NULL or NOT_IN_FLAG = '1' ) # MAGIC ) tblmkt # MAGIC on nvl(prod.ATC1_COD,'') = case when tblmkt.ATC1_Code is null then nvl(prod.ATC1_COD,'') else tblmkt.ATC1_Code end # MAGIC and nvl(prod.ATC2_COD,'') = case when tblmkt.ATC2_Code is null then nvl(prod.ATC2_COD,'') else tblmkt.ATC2_Code end # MAGIC and nvl(prod.ATC3_COD,'') = case when tblmkt.ATC3_Code is null then nvl(prod.ATC3_COD,'') else tblmkt.ATC3_Code end # MAGIC and nvl(prod.ATC4_COD,'') = case when tblmkt.ATC4_Code is null then nvl(prod.ATC4_COD,'') else tblmkt.ATC4_Code end # MAGIC and nvl(prod.APP1_COD,'') = case when tblmkt.NFC1_Code is null then nvl(prod.APP1_COD,'') else tblmkt.NFC1_Code end # MAGIC and nvl(prod.APP2_COD,'') = case when tblmkt.NFC2_Code is null then nvl(prod.APP2_COD,'') else tblmkt.NFC2_Code end # MAGIC and nvl(prod.APP3_COD,'') = case when tblmkt.NFC3_Code is null then nvl(prod.APP3_COD,'') else tblmkt.NFC3_Code end # MAGIC -------------------------------------------------------------------------------------- # MAGIC and nvl(prod.new_iqvia_prod_code, 0) = case when tblmkt.Product_Code is null then nvl(prod.new_iqvia_prod_code,0 ) else tblmkt.Product_Code end # MAGIC and nvl(prod.new_iqvia_pack_code, 0) = case when tblmkt.Pack_Code is null then nvl(prod.new_iqvia_pack_code,0 ) else tblmkt.Pack_Code end # MAGIC -------------------------------------------------------------------------------------- # MAGIC and right(concat('000000',nvl(prod.cmps_cod,0 )),6) = case when tblmkt.Molecule_Code is null then right(concat('000000',nvl(prod.cmps_cod,0 )),6) else right(concat('000000',nvl(tblmkt.Molecule_Code,0 )),6) end # MAGIC where tblmkt.market is not null # MAGIC ) # MAGIC , tmp2 as ( # MAGIC select distinct # MAGIC prod.iqvia_lineno # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code & prod_code # MAGIC ,prod.new_iqvia_pack_code as iqvia_pack_code # MAGIC ,prod.new_iqvia_prod_code as PROD_COD # MAGIC ------------------------------------------------------------------- # MAGIC ,prod.iqvia_notes # MAGIC ,prod.new_pack_flag # MAGIC ,prod.is_exists_chpa # MAGIC ,prod.product_id # MAGIC ,prod.prescription_nature # MAGIC ,prod.medicine_type # MAGIC ,prod.zk_medicine_tier1 # MAGIC ,prod.zk_medicine_tier2 # MAGIC ,prod.zk_medicine_tier3 # MAGIC ,prod.zk_medicine_tier4 # MAGIC ,prod.common_name # MAGIC ,prod.dosage_form # MAGIC ,prod.user_type # MAGIC ,prod.category_name # MAGIC ,prod.product_name # MAGIC ,prod.brand_name # MAGIC ,prod.zk_manu_des # MAGIC ,prod.zk_corp_des # MAGIC ,prod.zk_pack_des # MAGIC ,prod.counting_unit_a # MAGIC ,prod.dosage_unit_a # MAGIC ,prod.unit_a # MAGIC ,prod.app1_cod # MAGIC ,prod.app1_des # MAGIC ,prod.app1_des_c # MAGIC ,prod.app2_cod # MAGIC ,prod.app2_des # MAGIC ,prod.app2_des_c # MAGIC ,prod.app3_cod # MAGIC ,prod.app3_des # MAGIC ,prod.app3_des_c # MAGIC ,prod.atc1_cod # MAGIC ,prod.atc1_des # MAGIC ,prod.atc1_des_c # MAGIC ,prod.atc2_cod # MAGIC ,prod.atc2_des # MAGIC ,prod.atc2_des_c # MAGIC ,prod.atc3_cod # MAGIC ,prod.atc3_des # MAGIC ,prod.atc3_des_c # MAGIC ,prod.atc4_cod # MAGIC ,prod.atc4_des # MAGIC ,prod.atc4_des_c # MAGIC ,prod.bio_desc # MAGIC ,right(concat('000000',nvl(prod.cmps_cod,0 )),6) cmps_cod # MAGIC ,prod.cmps_des # MAGIC ,prod.cmps_des_c # MAGIC ,prod.corp_cod # MAGIC ,prod.corp_des # MAGIC ,prod.corp_des_c # MAGIC ,prod.edl_desc # MAGIC ,prod.eth_otc_desc # MAGIC ,prod.gene_orig_desc # MAGIC ,prod.gqce_desc # MAGIC ,prod.manu_cod # MAGIC ,prod.manu_des # MAGIC ,prod.manu_des_c # MAGIC ,prod.mnfl_cod # MAGIC ,prod.mnfl_des # MAGIC ,prod.nrdl_desc # MAGIC ,prod.pack_des # MAGIC ,prod.stgh_des # MAGIC ,prod.pack_lch # MAGIC ,prod.paed_desc # MAGIC ,prod.prod_des # MAGIC ,prod.prod_des_c # MAGIC ,prod.tcm_desc # MAGIC ,prod.vbp_desc # MAGIC ,prod.unit # MAGIC ,prod.counting_unit # MAGIC ,prod.dosage_unit # MAGIC ,nvl(MOLE.NRDL_Entry_Date,'') AS NRDL_ENTRY_DATE # MAGIC ,tblmkt.market # MAGIC ,tblmkt.bu # MAGIC ,nvl(tblmkt.extend_market_ratio,1) as extend_market_ratio # MAGIC ,1 as f_flag # MAGIC from tmp_pack_property prod # MAGIC LEFT JOIN tmp_pack_additional_attribute MOLE # MAGIC ---------------------------------------------------------------------------------- # MAGIC ON prod.new_iqvia_pack_code = MOLE.new_pack_code # MAGIC ---------------------------------------------------------------------------------- # MAGIC left join ( # MAGIC select market # MAGIC ,bu # MAGIC ,atc1_code # MAGIC ,atc2_code # MAGIC ,atc3_code # MAGIC ,atc4_code # MAGIC ,nfc1_code # MAGIC ,nfc2_code # MAGIC ,nfc3_code # MAGIC ,Molecule_Code # MAGIC ,zk_common_name # MAGIC ,extend_market_ratio # MAGIC ,zk_product_id # MAGIC ,zk_dosage_form # MAGIC ------------------------------------- # MAGIC ,new_pack_code as pack_code # MAGIC ,new_prod_code as Product_Code # MAGIC ------------------------------------- # MAGIC ,not_in_flag # MAGIC ,extend_market # MAGIC from tmp_tblmarket # MAGIC -- where (market not in( 'ZOK Default Market', 'Inhaled Extended Market') or (case when market in( 'Inhaled Extended Market') then zk_product_id is not null end)) # MAGIC where 1=1 # MAGIC and Extend_Market IS NULL and NOT_IN_FLAG ='0' # MAGIC # MAGIC )tblmkt # MAGIC on nvl(prod.ATC1_COD,'') = case when tblmkt.ATC1_Code is null then nvl(prod.ATC1_COD,'') else tblmkt.ATC1_Code end # MAGIC and nvl(prod.ATC2_COD,'') = case when tblmkt.ATC2_Code is null then nvl(prod.ATC2_COD,'') else tblmkt.ATC2_Code end # MAGIC and nvl(prod.ATC3_COD,'') = case when tblmkt.ATC3_Code is null then nvl(prod.ATC3_COD,'') else tblmkt.ATC3_Code end # MAGIC and nvl(prod.ATC4_COD,'') = case when tblmkt.ATC4_Code is null then nvl(prod.ATC4_COD,'') else tblmkt.ATC4_Code end # MAGIC and nvl(prod.APP1_COD,'') = case when tblmkt.NFC1_Code is null then nvl(prod.APP1_COD,'') else tblmkt.NFC1_Code end # MAGIC and nvl(prod.APP2_COD,'') = case when tblmkt.NFC2_Code is null then nvl(prod.APP2_COD,'') else tblmkt.NFC2_Code end # MAGIC and nvl(prod.APP3_COD,'') = case when tblmkt.NFC3_Code is null then nvl(prod.APP3_COD,'') else tblmkt.NFC3_Code end # MAGIC -------------------------------------------------------------------------------------- # MAGIC and nvl(prod.new_iqvia_prod_code, 0) = case when tblmkt.Product_Code is null then nvl(prod.new_iqvia_prod_code,0 ) else tblmkt.Product_Code end # MAGIC and nvl(prod.new_iqvia_pack_code, 0) = case when tblmkt.Pack_Code is null then nvl(prod.new_iqvia_pack_code,0 ) else tblmkt.Pack_Code end # MAGIC -------------------------------------------------------------------------------------- # MAGIC and right(concat('000000',nvl(prod.cmps_cod,0 )),6) = case when tblmkt.Molecule_Code is null then right(concat('000000',nvl(prod.cmps_cod,0 )),6) else right(concat('000000',nvl(tblmkt.Molecule_Code,0 )),6) end # MAGIC where tblmkt.market is not null # MAGIC # MAGIC ) # MAGIC , tmp3 as ( # MAGIC select distinct # MAGIC tmp1.iqvia_lineno # MAGIC ,tmp1.iqvia_pack_code # MAGIC ,tmp1.PROD_COD # MAGIC ,tmp1.iqvia_notes # MAGIC ,tmp1.new_pack_flag # MAGIC ,tmp1.is_exists_chpa # MAGIC ,tmp1.product_id # MAGIC ,tmp1.prescription_nature # MAGIC ,tmp1.medicine_type # MAGIC ,tmp1.zk_medicine_tier1 # MAGIC ,tmp1.zk_medicine_tier2 # MAGIC ,tmp1.zk_medicine_tier3 # MAGIC ,tmp1.zk_medicine_tier4 # MAGIC ,tmp1.common_name # MAGIC ,tmp1.dosage_form # MAGIC ,tmp1.user_type # MAGIC ,tmp1.category_name # MAGIC ,tmp1.product_name # MAGIC ,tmp1.brand_name # MAGIC ,tmp1.zk_manu_des # MAGIC ,tmp1.zk_corp_des # MAGIC ,tmp1.zk_pack_des # MAGIC ,tmp1.counting_unit_a # MAGIC ,tmp1.dosage_unit_a # MAGIC ,tmp1.unit_a # MAGIC ,tmp1.app1_cod # MAGIC ,tmp1.app1_des # MAGIC ,tmp1.app1_des_c # MAGIC ,tmp1.app2_cod # MAGIC ,tmp1.app2_des # MAGIC ,tmp1.app2_des_c # MAGIC ,tmp1.app3_cod # MAGIC ,tmp1.app3_des # MAGIC ,tmp1.app3_des_c # MAGIC ,tmp1.atc1_cod # MAGIC ,tmp1.atc1_des # MAGIC ,tmp1.atc1_des_c # MAGIC ,tmp1.atc2_cod # MAGIC ,tmp1.atc2_des # MAGIC ,tmp1.atc2_des_c # MAGIC ,tmp1.atc3_cod # MAGIC ,tmp1.atc3_des # MAGIC ,tmp1.atc3_des_c # MAGIC ,tmp1.atc4_cod # MAGIC ,tmp1.atc4_des # MAGIC ,tmp1.atc4_des_c # MAGIC ,tmp1.bio_desc # MAGIC ,tmp1.cmps_cod # MAGIC ,tmp1.cmps_des # MAGIC ,tmp1.cmps_des_c # MAGIC ,tmp1.corp_cod # MAGIC ,tmp1.corp_des # MAGIC ,tmp1.corp_des_c # MAGIC ,tmp1.edl_desc # MAGIC ,tmp1.eth_otc_desc # MAGIC ,tmp1.gene_orig_desc # MAGIC ,tmp1.gqce_desc # MAGIC ,tmp1.manu_cod # MAGIC ,tmp1.manu_des # MAGIC ,tmp1.manu_des_c # MAGIC ,tmp1.mnfl_cod # MAGIC ,tmp1.mnfl_des # MAGIC ,tmp1.nrdl_desc # MAGIC ,tmp1.pack_des # MAGIC ,tmp1.stgh_des # MAGIC ,tmp1.pack_lch # MAGIC ,tmp1.paed_desc # MAGIC ,tmp1.prod_des # MAGIC ,tmp1.prod_des_c # MAGIC ,tmp1.tcm_desc # MAGIC ,tmp1.vbp_desc # MAGIC ,tmp1.unit # MAGIC ,tmp1.counting_unit # MAGIC ,tmp1.dosage_unit # MAGIC ,tmp1.NRDL_ENTRY_DATE # MAGIC ,tmp1.market # MAGIC ,nvl(t2.bu,tmp1.bu) as bu # MAGIC ,tmp1.extend_market_ratio # MAGIC ,IF(tmp1.corp_des_c in('阿斯利康制药集团') ,'Y','N') is_az # MAGIC from tmp1 left join tmp2 # MAGIC on nvl(tmp1.ATC1_COD,'') = nvl(tmp2.ATC1_COD,'') # MAGIC and nvl(tmp1.ATC2_COD,'') = nvl(tmp2.ATC2_COD,'') # MAGIC and nvl(tmp1.ATC3_COD,'') = nvl(tmp2.ATC3_COD,'') # MAGIC and nvl(tmp1.ATC4_COD,'') = nvl(tmp2.ATC4_COD,'') # MAGIC and nvl(tmp1.APP1_COD,'') = nvl(tmp2.APP1_COD,'') # MAGIC and nvl(tmp1.APP2_COD,'') = nvl(tmp2.APP2_COD,'') # MAGIC and nvl(tmp1.APP3_COD,'') = nvl(tmp2.APP3_COD,'') # MAGIC and nvl(tmp1.PROD_COD,'') = nvl(tmp2.PROD_COD,'') # MAGIC and nvl(tmp1.iqvia_pack_code,'') = nvl(tmp2.iqvia_pack_code,'') # MAGIC and nvl(tmp1.common_name ,'') = nvl(tmp2.common_name ,'') # MAGIC and nvl(tmp1.product_id,'') = nvl(tmp2.product_id,'') # MAGIC and nvl(tmp1.dosage_form ,'') = nvl(tmp2.dosage_form ,'') # MAGIC and nvl(tmp1.CMPS_COD,'') = nvl(tmp2.CMPS_COD,'') # MAGIC and nvl(tmp1.market ,'') = nvl(tmp2.market ,'') # MAGIC left join (select Market, BU,Extend_Market,Extend_Market_Ratio from dwd.dwd_gnd_ext_retail_tblmarket # MAGIC -- where (market not in( 'ZOK Default Market', 'Inhaled Extended Market') and Extend_Market IS NOT NULL # MAGIC -- )or (case when market in( 'Inhaled Extended Market') then zk_product_id is not null end) # MAGIC where Extend_Market IS NOT NULL # MAGIC ) t2 on tmp1.Market=t2.Extend_Market # MAGIC where NVL(tmp2.f_flag,0) !=1 # MAGIC # MAGIC ) # MAGIC # MAGIC -- 合并所有市场数据 # MAGIC select # MAGIC * # MAGIC from tmp3 # MAGIC --没有匹配上的给默认市场：Non AZ Retail Related Market # MAGIC union all # MAGIC select # MAGIC prod.iqvia_lineno # MAGIC ------------------------------------------------------------------- # MAGIC -- format pack_code & prod_code # MAGIC ,prod.new_iqvia_pack_code as iqvia_pack_code # MAGIC ,prod.new_iqvia_prod_code as PROD_COD # MAGIC ------------------------------------------------------------------- # MAGIC ,prod.iqvia_notes # MAGIC ,prod.new_pack_flag # MAGIC ,prod.is_exists_chpa # MAGIC ,prod.product_id # MAGIC ,prod.prescription_nature # MAGIC ,prod.medicine_type # MAGIC ,prod.zk_medicine_tier1 # MAGIC ,prod.zk_medicine_tier2 # MAGIC ,prod.zk_medicine_tier3 # MAGIC ,prod.zk_medicine_tier4 # MAGIC ,prod.common_name # MAGIC ,prod.dosage_form # MAGIC ,prod.user_type # MAGIC ,prod.category_name # MAGIC ,prod.product_name # MAGIC ,prod.brand_name # MAGIC ,prod.zk_manu_des # MAGIC ,prod.zk_corp_des # MAGIC ,prod.zk_pack_des # MAGIC ,prod.counting_unit_a # MAGIC ,prod.dosage_unit_a # MAGIC ,prod.unit_a # MAGIC ,prod.app1_cod # MAGIC ,prod.app1_des # MAGIC ,prod.app1_des_c # MAGIC ,prod.app2_cod # MAGIC ,prod.app2_des # MAGIC ,prod.app2_des_c # MAGIC ,prod.app3_cod # MAGIC ,prod.app3_des # MAGIC ,prod.app3_des_c # MAGIC ,prod.atc1_cod # MAGIC ,prod.atc1_des # MAGIC ,prod.atc1_des_c # MAGIC ,prod.atc2_cod # MAGIC ,prod.atc2_des # MAGIC ,prod.atc2_des_c # MAGIC ,prod.atc3_cod # MAGIC ,prod.atc3_des # MAGIC ,prod.atc3_des_c # MAGIC ,prod.atc4_cod # MAGIC ,prod.atc4_des # MAGIC ,prod.atc4_des_c # MAGIC ,prod.bio_desc # MAGIC ,right(concat('00000',nvl(prod.cmps_cod,0 )),6) cmps_cod # MAGIC ,prod.cmps_des # MAGIC ,prod.cmps_des_c # MAGIC ,prod.corp_cod # MAGIC ,prod.corp_des # MAGIC ,prod.corp_des_c # MAGIC ,prod.edl_desc # MAGIC ,prod.eth_otc_desc # MAGIC ,prod.gene_orig_desc # MAGIC ,prod.gqce_desc # MAGIC ,prod.manu_cod # MAGIC ,prod.manu_des # MAGIC ,prod.manu_des_c # MAGIC ,prod.mnfl_cod # MAGIC ,prod.mnfl_des # MAGIC ,prod.nrdl_desc # MAGIC ,prod.pack_des # MAGIC ,prod.stgh_des # MAGIC ,prod.pack_lch # MAGIC ,prod.paed_desc # MAGIC ,prod.prod_des # MAGIC ,prod.prod_des_c # MAGIC ,prod.tcm_desc # MAGIC ,prod.vbp_desc # MAGIC ,prod.unit # MAGIC ,prod.counting_unit # MAGIC ,prod.dosage_unit # MAGIC ,nvl(MOLE.NRDL_Entry_Date,'') AS NRDL_ENTRY_DATE # MAGIC ,'Non AZ Retail Related Market' as market # MAGIC ,tmp3.bu # MAGIC ,nvl(tmp3.extend_market_ratio,1) as extend_market_ratio # MAGIC ,IF(PROD.corp_des_c in('阿斯利康制药集团') ,'Y','N') is_az # MAGIC from tmp_pack_property prod # MAGIC left join tmp3 on prod.product_id = tmp3.product_id # MAGIC LEFT JOIN tmp_pack_additional_attribute MOLE # MAGIC ---------------------------------------------------------------------------------- # MAGIC ON prod.new_iqvia_pack_code = MOLE.new_pack_code # MAGIC ---------------------------------------------------------------------------------- # MAGIC where tmp3.product_id is null # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.1 generate mapping table # MAGIC -- 1.1.3 获取market信息，产品重新归属 # MAGIC -- 1.1.3.2 对配置表的pack 对应公司、分子式 根据 dwd_gnd_tbl_corp_change 表进行产品重新归属 # MAGIC -- pack_flag ：0-倒减的others 1-不拆的 2-拆的 # MAGIC -- brand_flag ：0-倒减的others 1-买了这个品牌 2-没买这个品牌 # MAGIC ------------------------------------------------------------------------------------- # MAGIC insert overwrite table tmp.tmp_zk_retail_pack_property_corp # MAGIC select distinct a.iqvia_pack_code # MAGIC ,COALESCE(c.corp_cod,b.corp_cod,a.corp_cod) corp_cod # MAGIC ,COALESCE(c.corp_des,b.corp_des,a.corp_des) corp_des # MAGIC ,COALESCE(d.corp_des_c,a.corp_des_c ) corp_des_c # MAGIC ,COALESCE(c.manu_cod,b.manu_cod,a.manu_cod) manu_cod # MAGIC ,COALESCE(c.manu_des,b.manu_des,a.manu_des) manu_des # MAGIC ,COALESCE(e.manu_des_c,a.manu_des_c) manu_des_c # MAGIC from ( # MAGIC select # MAGIC DISTINCT # MAGIC iqvia_pack_code, # MAGIC corp_cod, # MAGIC corp_des, # MAGIC corp_des_c, # MAGIC right(concat('000000',manu_cod),6 ) manu_cod, # MAGIC manu_des, # MAGIC manu_des_c, # MAGIC prod_cod # MAGIC from tmp.tmp_zk_retail_pack_property # MAGIC ) a # MAGIC left join ( # MAGIC select # MAGIC ---------------------------------------------- # MAGIC -- format prod_cod # MAGIC case when length(prod_cod) < 9 then right(concat('000000000',prod_cod),9) else prod_cod end as prod_cod, # MAGIC ---------------------------------------------- # MAGIC corp_cod, # MAGIC corp_des, # MAGIC right(concat('000000',manu_cod),6 ) manu_cod, # MAGIC manu_des # MAGIC from dwd.dwd_gnd_tbl_corp_change where pack_cod is null # MAGIC ) b on a.prod_cod = b.prod_cod # MAGIC left join ( # MAGIC select # MAGIC ---------------------------------------------- # MAGIC -- format pack_cod # MAGIC case when length(trim(pack_cod)) < 12 and trim(pack_cod) REGEXP '^[0-9]' then right(concat('000000000000',trim(pack_cod)),12) else trim(pack_cod) end as pack_cod, # MAGIC ---------------------------------------------- # MAGIC corp_cod, # MAGIC corp_des, # MAGIC right(concat('000000',manu_cod),6 ) manu_cod, # MAGIC manu_des # MAGIC from dwd.dwd_gnd_tbl_corp_change # MAGIC where pack_cod is not null # MAGIC ) c on a.iqvia_pack_code = c.pack_cod # MAGIC left join ( # MAGIC select distinct corp_cod,corp_des_c # MAGIC from dwd.dwd_gnd_ext_retail_pack_property # MAGIC ) d on coalesce(C.corp_cod,B.corp_cod ) = d.corp_cod # MAGIC Left join ( # MAGIC select distinct right(concat('000000',manu_cod),6 ) manu_cod,manu_des,manu_des_c # MAGIC from dwd.dwd_gnd_ext_retail_pack_property # MAGIC ) e On coalesce(C.manu_cod,B.manu_cod ) = e.manu_cod # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.1 generate mapping table # MAGIC -- 1.1.4 update special case 'CV-其他美托洛尔' # MAGIC -- “酒石酸美托洛尔”和“琥珀酸美托洛尔”以及部分others分子式标准名都为“美托洛尔” # MAGIC -- 这里需要根据市场把“酒石酸美托洛尔”以及这部分others区分出来，不能应用“琥珀酸美托洛尔”即level_molecule为“美托洛尔”的拆分比例 # MAGIC -- 区分方式为取market为非“Metoprolol Succinate Market”即非“酒石酸美托洛尔市场”的pack_code # MAGIC ------------------------------------------------------------------------------------- # MAGIC UPDATE tmp.tmp_retail_pack_mapping # MAGIC SET # MAGIC level_molecule = '其他美托洛尔', # MAGIC brand_flag = 3 # MAGIC where level_ta = 'CV' # MAGIC and level_market = '高血压用药' # MAGIC and level_molecule = '美托洛尔' # MAGIC and pack_code not in ( # MAGIC select distinct iqvia_pack_code # MAGIC from tmp.tmp_zk_retail_pack_property # MAGIC where market = 'Metoprolol Succinate Market' # MAGIC ) # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.2 update the pack data extra column in tmp.tmp_retail_pack_rawdata when pack data has mapping # MAGIC ------------------------------------------------------------------------------------- # MAGIC # MAGIC merge into tmp.tmp_retail_pack_rawdata a # MAGIC using( # MAGIC select * from tmp.tmp_retail_pack_mapping # MAGIC ) as b # MAGIC on a.product_id = b.product_id # MAGIC and a.level_ta = b.level_ta # MAGIC and a.level_market = b.level_market # MAGIC when matched then # MAGIC update set # MAGIC a.pack_code = b.pack_code, # MAGIC a.molecule_code = b.molecule_code, # MAGIC a.molecule_desc = b.molecule_desc, # MAGIC a.product_code = b.product_code, # MAGIC a.product_desc = b.product_desc, # MAGIC a.level_molecule = b.level_molecule, # MAGIC a.level_brand = b.level_brand, # MAGIC a.brand_flag = b.brand_flag # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.3 update the pack data extra column in tmp.tmp_retail_pack_rawdata when pack data without mapping # MAGIC ------------------------------------------------------------------------------------- # MAGIC # MAGIC with tmp_pack_property as ( # MAGIC --------------------------------------------------------------- # MAGIC -- 以数字开头的iqvia_pack_code调整为12位（以字母开头的不变），不足位数补0 # MAGIC -- iqvia_prod_code调整为9位，不足位数补0 # MAGIC select # MAGIC case when length(trim(iqvia_pack_code)) < 12 and trim(iqvia_pack_code) REGEXP '^[0-9]' then right(concat('000000000000',trim(iqvia_pack_code)),12) else trim(iqvia_pack_code) end as iqvia_pack_code, # MAGIC case when length(iqvia_prod_code) < 9 then right(concat('000000000',iqvia_prod_code),9) else iqvia_prod_code end as iqvia_prod_code, # MAGIC product_id, # MAGIC cmps_des, # MAGIC cmps_des_c, # MAGIC prod_des, # MAGIC prod_des_c, # MAGIC corp_cod, # MAGIC app1_cod, # MAGIC app2_cod, # MAGIC app3_cod # MAGIC from dwd.dwd_gnd_ext_retail_pack_property # MAGIC ), tmp_b2c_label_total as ( # MAGIC select # MAGIC case when length(IQVIA_PROD_CODE) < 9 then right(concat('000000000',IQVIA_PROD_CODE),9) else IQVIA_PROD_CODE end as IQVIA_PROD_CODE, # MAGIC ZK_Prod_C, # MAGIC APP1_COD, # MAGIC APP2_COD, # MAGIC APP3_COD # MAGIC from dwd.dwd_inc_gnd_retail_b2c_label_total # MAGIC --------------------------------------------------------------- # MAGIC ), tmp_suffix_brand as ( # MAGIC select distinct split(brand,'-')[0] # MAGIC from dwd.dwd_gnd_retail_split_automatic # MAGIC where brand like '%-%' # MAGIC ), tmp_mapping as ( # MAGIC select # MAGIC distinct # MAGIC a.product_id as product_id, # MAGIC a.iqvia_pack_code as pack_code, # MAGIC a.cmps_des as molecule_code, # MAGIC a.cmps_des_c as molecule_name, # MAGIC ifnull(a.prod_des, concat(a.cmps_des,'_OTHER_PROD')) as product_code, # MAGIC ifnull(a.prod_des_c, concat(a.cmps_des_c,'_OTHER_PROD')) as product_name # MAGIC from tmp_pack_property a # MAGIC where a.prod_des_c not in (select * from tmp_suffix_brand) # MAGIC or a.prod_des_c is null # MAGIC # MAGIC union all # MAGIC # MAGIC select # MAGIC distinct # MAGIC a.product_id as product_id, # MAGIC a.iqvia_pack_code as pack_code, # MAGIC a.cmps_des as molecule_code, # MAGIC a.cmps_des_c as molecule_name, # MAGIC a.prod_des as product_code, # MAGIC ifnull(b.ZK_Prod_C, a.prod_des_c) as product_name # MAGIC from tmp_pack_property a # MAGIC left join tmp_b2c_label_total b # MAGIC on a.iqvia_prod_code = b.IQVIA_PROD_CODE # MAGIC and a.app1_cod = b.APP1_COD # MAGIC and a.app2_cod = b.APP2_COD # MAGIC and a.app3_cod = b.APP3_COD # MAGIC where a.prod_des_c in (select * from tmp_suffix_brand) # MAGIC ) # MAGIC # MAGIC merge into tmp.tmp_retail_pack_rawdata a # MAGIC using( # MAGIC select * from tmp_mapping # MAGIC ) as b # MAGIC on a.product_id = b.product_id # MAGIC and a.pack_code is null # MAGIC when matched then # MAGIC update set # MAGIC a.pack_code = b.pack_code, # MAGIC a.molecule_code = b.molecule_code, # MAGIC a.molecule_desc = b.molecule_name, # MAGIC a.product_code = b.product_code, # MAGIC a.product_desc = b.product_name, # MAGIC a.brand_flag = 3 # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.4 update the pack data brand flag to '1' which pack has regional data # MAGIC ------------------------------------------------------------------------------------- # MAGIC with tmp_has_regional_data as ( # MAGIC select # MAGIC distinct # MAGIC quarter, # MAGIC product_id, # MAGIC pack_code # MAGIC from tmp.tmp_retail_pack_rawdata # MAGIC where zk_regin <> '全国' # MAGIC ) # MAGIC # MAGIC merge into tmp.tmp_retail_pack_rawdata a # MAGIC using( # MAGIC select * from tmp_has_regional_data # MAGIC ) as b # MAGIC on a.quarter = b.quarter # MAGIC and a.product_id = b.product_id # MAGIC and a.pack_code = b.pack_code # MAGIC when matched then # MAGIC update set # MAGIC a.brand_flag = 1 # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-1: generate product_id&level relationship mapping table # MAGIC -- 1.5 delete non GI data # MAGIC -- “TA=GI”的数据，只取market为'Antacid anti GI swelling agent+PPI Oral'的数 # MAGIC -- 非“TA=GI“的数据，无限制 # MAGIC -- 此处筛选并移除TA为GI，但并未在此市场出现的数据 # MAGIC ------------------------------------------------------------------------------------- # MAGIC delete from tmp.tmp_retail_pack_rawdata a # MAGIC where a.level_ta = 'GI' # MAGIC and a.pack_code not in ( # MAGIC select distinct iqvia_pack_code # MAGIC from tmp.tmp_zk_retail_pack_property # MAGIC where market = 'PPI Oral Market' # MAGIC ) # COMMAND ---------- # MAGIC %md # MAGIC ## STEP-2: split regional data by brand ratio # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-2: split regional data by brand ratio when there is no regional data and there is a brand ratio # MAGIC -- only split the pack data with brand ratio and without regional pack data # MAGIC ------------------------------------------------------------------------------------- # MAGIC # MAGIC with tmp_has_regional_data as ( # MAGIC select # MAGIC distinct # MAGIC quarter, # MAGIC product_id # MAGIC from tmp.tmp_retail_pack_rawdata # MAGIC where zk_regin <> '全国' # MAGIC ), tmp_has_brand_ratio as ( # MAGIC select # MAGIC distinct # MAGIC b.product_id # MAGIC from tmp.tmp_retail_level_brand a # MAGIC inner join tmp.tmp_retail_pack_mapping b # MAGIC on a.ta = b.level_ta # MAGIC and a.market = b.level_market # MAGIC and a.molecule = b.level_molecule # MAGIC and a.brand = b.level_brand # MAGIC ), tmp_pack_rawdata as ( # MAGIC select * # MAGIC from tmp.tmp_retail_pack_rawdata a # MAGIC where not exists( # MAGIC select b.quarter,b.product_id from tmp_has_regional_data b # MAGIC where a.quarter = b.quarter # MAGIC and a.product_id = b.product_id # MAGIC ) and exists( # MAGIC select c.product_id from tmp_has_brand_ratio c # MAGIC where a.product_id = c.product_id # MAGIC ) # MAGIC ) # MAGIC ----------------------------- append split regional data ---------------------------- # MAGIC insert into table tmp.tmp_retail_pack_rawdata # MAGIC # MAGIC select # MAGIC c.product_id, # MAGIC c.zk_product_id, # MAGIC a.province_city, # MAGIC c.zk_rx_otc, # MAGIC c.zk_medicine_type, # MAGIC c.zk_medicine_tier1, # MAGIC c.zk_medicine_tier2, # MAGIC c.zk_medicine_tier3, # MAGIC c.zk_medicine_tier4, # MAGIC c.zk_common_name, # MAGIC c.zk_dosage_form, # MAGIC c.zk_user_type, # MAGIC c.zk_category_name, # MAGIC c.zk_product_name, # MAGIC c.zk_brand_name, # MAGIC c.zk_manu_des, # MAGIC c.zk_corp_des, # MAGIC c.zk_pack_des, # MAGIC c.month, # MAGIC c.quarter, # MAGIC c.price, # MAGIC round(c.sales_unit * a.ratio_vol ,10) as sales_unit, # MAGIC round(c.sales_value * a.ratio_val ,10) as sales_value, # MAGIC c.digital_spread_rate, # MAGIC c.weighted_spread_rate, # MAGIC c.counting_unit, # MAGIC c.pack_code, # MAGIC c.molecule_code, # MAGIC c.molecule_desc, # MAGIC c.product_code, # MAGIC c.product_desc, # MAGIC c.level_ta, # MAGIC c.level_market, # MAGIC c.level_molecule, # MAGIC c.level_brand, # MAGIC a.ratio_val, # MAGIC a.ratio_vol, # MAGIC 3 as data_flag, # MAGIC b.brand_flag # MAGIC from tmp.tmp_retail_level_brand a # MAGIC inner join tmp.tmp_retail_pack_mapping b # MAGIC on a.ta = b.level_ta # MAGIC and a.market = b.level_market # MAGIC and a.molecule = b.level_molecule # MAGIC and a.brand = b.level_brand # MAGIC inner join tmp_pack_rawdata c # MAGIC on c.product_id = b.product_id # MAGIC and c.quarter = a.quarter # MAGIC where a.province_city <> '全国' # MAGIC # COMMAND ---------- # MAGIC %md # MAGIC ## STEP-3: substract ROC data # COMMAND ---------- # MAGIC %sql # MAGIC ------------------------------------------------------------------------------------- # MAGIC -- STEP-3: substract ROC data # MAGIC -- if there is regional pack data or splited regional pack data, ROC = '全国' - 'sum regional data' # MAGIC -- if there is not regional pack data, ROC = '全国' # MAGIC ------------------------------------------------------------------------------------- # MAGIC # MAGIC with tmp_area_group as ( # MAGIC select # MAGIC product_id, # MAGIC quarter, # MAGIC month, # MAGIC sum(sales_unit) as sales_unit, # MAGIC sum(sales_value) as sales_value # MAGIC from tmp.tmp_retail_pack_rawdata # MAGIC where zk_regin <> '全国' # MAGIC group by product_id,quarter,month # MAGIC ), tmp_has_regional_data as ( # MAGIC select # MAGIC distinct # MAGIC quarter, # MAGIC product_id # MAGIC from tmp.tmp_retail_pack_rawdata # MAGIC where zk_regin <> '全国' # MAGIC and data_flag = 0 # MAGIC ) # MAGIC # MAGIC ----------------------------- append ROC data ------------------------------------------ # MAGIC insert into table tmp.tmp_retail_pack_rawdata # MAGIC # MAGIC select # MAGIC a.product_id, # MAGIC a.zk_product_id, # MAGIC 'ROC', # MAGIC a.zk_rx_otc, # MAGIC a.zk_medicine_type, # MAGIC a.zk_medicine_tier1, # MAGIC a.zk_medicine_tier2, # MAGIC a.zk_medicine_tier3, # MAGIC a.zk_medicine_tier4, # MAGIC a.zk_common_name, # MAGIC a.zk_dosage_form, # MAGIC a.zk_user_type, # MAGIC a.zk_category_name, # MAGIC a.zk_product_name, # MAGIC a.zk_brand_name, # MAGIC a.zk_manu_des, # MAGIC a.zk_corp_des, # MAGIC a.zk_pack_des, # MAGIC a.month, # MAGIC a.quarter, # MAGIC a.price, # MAGIC a.sales_unit - ifnull(b.sales_unit,0) as sales_unit, # MAGIC a.sales_value - ifnull(b.sales_value,0) as sales_value, # MAGIC a.digital_spread_rate, # MAGIC a.weighted_spread_rate, # MAGIC a.counting_unit, # MAGIC a.pack_code, # MAGIC a.molecule_code, # MAGIC a.molecule_desc, # MAGIC a.product_code, # MAGIC a.product_desc, # MAGIC a.level_ta, # MAGIC a.level_market, # MAGIC a.level_molecule, # MAGIC a.level_brand, # MAGIC a.ratio_val, # MAGIC a.ratio_vol, # MAGIC ------------------------------------------------------------------------------- # MAGIC -- 有省份pack的数（包括无品牌数据的数）计算出来的ROC所对应的pack_flag，也需要是1-原始值，不能是2-拆分的值 # MAGIC -- 所以这里针对这类有省份数据的ROC数据，data_flag赋值为0-原始值，其他的仍为1-倒减值 # MAGIC case when c.product_id is not null then 0 else 1 end as data_flag, # MAGIC ------------------------------------------------------------------------------- # MAGIC a.brand_flag # MAGIC from tmp.tmp_retail_pack_rawdata a # MAGIC inner join tmp_area_group b # MAGIC on a.product_id = b.product_id # MAGIC and a.quarter = b.quarter # MAGIC and nvl(a.month,'') = nvl(b.month,'') # MAGIC left join tmp_has_regional_data c # MAGIC on a.quarter = c.quarter # MAGIC and a.product_id = c.product_id # MAGIC where a.zk_regin = '全国' # COMMAND ---------- ############################################################END##############################################################