chenwu/MarketAnalysis-ETL
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
MarketAnalysis-ETL/XIEHE/bkp_03 dm_ext_xiehe_pack_property.sql
chenwu 832c7bcd69 new file: 01 dm_tf_external_sales.sql 
new file:   02 dm_td_external_keycompatitor.sql
	new file:   03 dm_td_external_brand_market.sql
	new file:   04 dm_td_external_calendar.sql
	new file:   05 dm_td_external_exchangerate.sql
	new file:   07 dm_td_external_packinfo.sql
	new file:   08 dm_td_external_corp.sql
	new file:   09 dm_td_external_geo_type.sql
	new file:   11 DM_TD_EXTERNAL_MARKET_NEW.sql
	new file:   12 dm_td_external_org.sql
	new file:   13 external auth.sql
	new file:   14 dm_tf_external_retail_special_bkp.sql
	new file:   AIA/01 dm_aia_pack_property.sql
	new file:   AIA/02 dm_ext_aia_sales.sql
	new file:   AIA/02 dm_ext_aia_sales_bakup_20230327.sql
	new file:   AIA/03 dm_aia_flag.sql
	new file:   AIA/04 dm_aia_provided_flag.sql
	new file:   AIA/06 DM_TD_EXT_AIA_PACK_PROPERTY.sql
	new file:   AIA/07 DM_TD_EXT_AIA_PACK2MARKET.sql
	new file:   AIA/08 DM_TD_EXT_AIA_MARKET_RATIO.sql
	new file:   AIA/09 DM_TD_EXT_AIA_MARKET_BRAND_RATIO.sql
	new file:   AIA/10 DM_TD_EXT_AIA_MARKET_PACK_MAPPING.sql
	new file:   AIA/11 DM_TD_EXT_AIA_TARGET_INST.sql
	new file:   AIA/11 dm_aia_targethp_flag.sql
	new file:   "AIA/z_03 dm_aia_flag_\345\244\207\344\273\275\347\224\250.sql"
	new file:   CHC/01 dm_chc_pack_property.sql
	new file:   CHC/02 DM_TF_EXT_CHC_SALES.sql
	new file:   CHC/03 DM_TD_EXT_CHC_PACK_PROPERTY.sql
	new file:   CHC/04 DM_TD_EXT_CHC_PACK2MARKET.sql
	new file:   CHC/05 DM_TD_EXT_CHC_MARKET_RATIO.sql
	new file:   CHC/06 DM_TD_EXT_CHC_MARKET_BRAND_RATIO.sql
	new file:   CHC/07 DM_TD_EXT_CHC_MARKET_PACK_MAPPING.sql
	new file:   CHPA/01 dwd_ims_atc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_nfc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_td_manufacturer_corp.sql
	new file:   CHPA/01 dwd_ims_td_pack_property.sql
	new file:   CHPA/01 dwd_update.sql
	new file:   CHPA/01_FB_BLOB_TO_DWD.sql
	new file:   CHPA/02 DWS_IMS_TD_GEO.sql
	new file:   CHPA/02 dws_ims_td_atc_cn.sql
	new file:   CHPA/02 dws_ims_td_corp_cn.sql
	new file:   CHPA/02 dws_ims_td_date.sql
	new file:   CHPA/02 dws_ims_td_manu_cn.sql
	new file:   CHPA/02 dws_ims_td_market.sql
	new file:   CHPA/02 dws_ims_td_market_ta.sql
	new file:   CHPA/02 dws_ims_td_nfc_cn.sql
	new file:   CHPA/02 dws_ims_td_prod_cn.sql
	new file:   CHPA/02 tmp_ims_td_prod_tmp.sql
	new file:   CHPA/02 tmp_ims_tf_fact_sales.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_BRAND_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_PACK_MAPPING.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK2MARKET.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK_PROPERTY.sql
	new file:   CHPA/03 DM_TF_EXT_CHPA_SALES.sql
	new file:   CHPA/03 dm_ims_td_calendar.sql
	new file:   CHPA/03 dm_ims_td_geo.sql
	new file:   CHPA/03 dm_ims_td_market_property.sql
	new file:   CHPA/03 dm_ims_td_org.sql
	new file:   CHPA/03 dm_ims_td_org_hvh.sql
	new file:   CHPA/03 dm_ims_td_pack_property.sql
	new file:   CHPA/03 dm_ims_tf_sales.sql
	new file:   CHPA/03 dm_td_chpa_market_definition.sql
	new file:   CHPA/03 dm_td_ims_city_mapping.sql
	new file:   EC/03 ec_load_data.sql
	new file:   EC/04 DM_TD_EXT_EC_PACK_PROPERTY.sql
	new file:   EC/05 DM_TF_EXT_EC_SALES.sql
	new file:   EC/06 DM_TD_EXT_EC_PACK2MARKET.sql
	new file:   EC/07 DM_TD_EXT_EC_MARKET_RATIO.sql
	new file:   EC/08 DM_TD_EXT_EC_MARKET_BRAND_RATIO.sql
	new file:   EC/09 DM_TD_EXT_EC_MARKET_PACK_MAPPING.sql
	new file:   EC/1 (ec)blob_to_dwd.sql
	new file:   EC/2 dwd_inc_gnd_ext_ec_nationnal_pack_union_all.py
	new file:   Merged_Data/Merged_Data_Config_table_bkp.sql
	new file:   Merged_Data/Merged_Data_Config_table_bymonth.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bkp.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bymonth_bkp.sql
	new file:   ORG/DM_TD_EXT_AIA_ORG.sql
	new file:   ORG/DM_TD_EXT_CHC_ORG.sql
	new file:   ORG/DM_TD_EXT_CHPA_ORG.sql
	new file:   ORG/DM_TD_EXT_COUNTY_ORG.sql
	new file:   ORG/DM_TD_EXT_EC_ORG.sql
	new file:   ORG/DM_TD_EXT_RETAIL_ORG.sql
	new file:   ORG/DM_TD_EXT_THC_ORG.sql
	new file:   ORG/DM_TD_EXT_XIEHE_ORG.sql
	new file:   OTHERS/01 dm_td_report_url.sql
	new file:   OTHERS/02 dws_ext_email_warning.sql
	new file:   OTHERS/external_triggered_email.py
	new file:   Retail/01 load_tmp_data.py
	new file:   Retail/02 split_brand_data.py
	new file:   Retail/03 split_pack_data.py
	new file:   Retail/04 map_to_dws_table.py
	new file:   Retail/05 load_dtp_temp_data.py
	new file:   Retail/06 split_dtp_brand_data.py
	new file:   Retail/07 split_dtp_pack_data.py
	new file:   Retail/08 map_to_dtp_dws_table.py
	new file:   Retail/09 dwd_inc_gnd_ext_retail_nataional.py
	new file:   Retail/10 map_to_retail_dm_table.py
	new file:   Retail/11 map_to_overview_dm_table.py
	new file:   Retail/12 dws_tf_external_retail_dtp_special.sql
	new file:   Retail/13 DM_TF_EXT_RETAIL_SALES.sql
	new file:   Retail/14 DM_TF_EXT_RETAIL_DTP_SALES.sql
	new file:   Retail/15 DM_TD_EXT_RETAIL_PACK_PROPERTY.sql
	new file:   Retail/16 DM_TD_EXT_RETAIL_DTP_PACK_PROPERTY.sql
	new file:   Retail/17 DM_TD_EXT_DTP_PACK2MARKET.sql
	new file:   Retail/17 DM_TD_EXT_RETAIL_PACK2MARKET.sql
	new file:   Retail/18 DM_TD_EXT_DTP_MARKET_RATIO.sql
	new file:   Retail/18 DM_TD_EXT_RETAIL_MARKET_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_DTP_MARKET_BRAND_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_RETAIL_MARKET_BRAND_RATIO.sql
	new file:   Retail/20 DM_TD_EXT_DTP_MARKET_PACK_MAPPING.sql
	new file:   Retail/20 DM_TD_EXT_RETAIL_MARKET_PACK_MAPPING.sql
	new file:   "Retail/z1 dwd_inc_gnd_ext_retail_nataional_\344\275\234\345\272\237.py"
	new file:   "Retail/z2 retail_load_data_\344\275\234\345\272\237.sql"
	new file:   "Retail/z3 retail_overview_data_\344\275\234\345\272\237.sql"
	new file:   THC/01 dm_thc_pack_property.sql
	new file:   THC/02 dm_ext_thc_sales.sql
	new file:   THC/02 dm_ext_thc_sales_bakup_20260327.sql
	new file:   THC/03 DM_TF_EXT_THC_MARKET_SALES_CHT.sql
	new file:   THC/04 dm_tf_external_sales_thc.sql
	new file:   THC/05 DM_TD_EXT_THC_PACK_PROPERTY.sql
	new file:   THC/06 DM_TD_EXT_THC_PACK2MARKET.sql
	new file:   THC/07 DM_TD_EXT_THC_MARKET_RATIO.sql
	new file:   THC/08 DM_TD_EXT_THC_MARKET_BRAND_RATIO.sql
	new file:   THC/09 DM_TD_EXT_THC_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_PACKINFO.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES_v1.0.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING_NIAD.sql
	new file:   XIEHE/01 xiehe_blob_to_dwd.py
	new file:   XIEHE/02 dm_xiehe_pack_property.sql
	new file:   XIEHE/03 dm_ext_xiehe_geo.sql
	new file:   XIEHE/04 dm_ext_xiehe_sales.sql
	new file:   XIEHE/05 dm_td_xiehe_core_dept.sql
	new file:   XIEHE/06 DM_TF_EXT_XIEHE_SALES.sql
	new file:   XIEHE/07 DM_TD_EXT_XIEHE_PACK_PROPERTY.sql
	new file:   XIEHE/08 DM_TD_EXT_XIEHE_PACK2MARKET.sql
	new file:   XIEHE/09 DM_TD_EXT_XIEHE_MARKET_RATIO.sql
	new file:   XIEHE/10 DM_TD_EXT_XIEHE_MARKET_BRAND_RATIO.sql
	new file:   XIEHE/11 DM_TD_EXT_XIEHE_MARKET_PACK_MAPPING.sql
	new file:   XIEHE/bkp_01 xiehe_blob2dwd.py
	new file:   XIEHE/bkp_02 dm_ext_xiehe_sales.sql
	new file:   XIEHE/bkp_03 dm_ext_xiehe_pack_property.sql
	new file:   county/01 tmp_ims_county_fact_sales_sum.sql
	new file:   county/02 tmp_imscounty_Result.sql
	new file:   county/03 dm_ims_td_county_geo.sql
	new file:   county/04 dws_ext_county_tf_sales.sql
	new file:   county/05 dm_ext_county_td_pack_property.sql
	new file:   county/06 dm_td_county_pack_region.sql
	new file:   county/07 dm_ext_county_tf_sales_region.sql
	new file:   county/08 DM_TD_EXT_COUNTY_PACK_PROPERTY.sql
	new file:   county/09 DM_TF_EXT_COUNTY_SALES.sql
	new file:   county/10 DM_TD_EXT_COUNTY_PACK2MARKET.sql
	new file:   county/11 DM_TD_EXT_COUNTY_MARKET_RATIO.sql
	new file:   county/12 DM_TD_EXT_COUNTY_MARKET_BRAND_RATIO.sql
	new file:   county/13 DM_TD_EXT_COUNTY_MARKET_PACK_MAPPING.sql
	new file:   for_AIA_Dashboard/01 dm_td_aia_inst_mkt.sql
	new file:   for_AIA_Dashboard/02 dm_td_aia_auth_sales.sql
	new file:   for_AIA_Dashboard/03 dm_td_aia_original_col.sql
	new file:   for_AIA_Dashboard/04 dm_td_aia_nosales_inst.sql
	new file:   for_AIA_Dashboard/05 dm_td_aia_is_eagle_flag.sql
	new file:   for_AIA_Dashboard/06 dm_td_aia_rank.sql
	new file:   for_AIA_Dashboard/07 dm_ext_aia_data_remove_flag.sql
	new file:   for_AIA_Dashboard/07 dm_td_aia_remove_special_ins_bkp.py
	new file:   for_AIA_Dashboard/08 dm_ext_aia_data_quality_flag.sql
	new file:   z 01 dm_tf_external_sales.sql
	new file:   "z 10 dm_td_external_market_pack_mapping_\344\275\234\345\272\237.sql"
	new file:   "z 11 dm_td_external_market_\344\275\234\345\272\237.sql"
	new file:   "\344\270\212\347\272\277\350\204\232\346\234\254.sql"
	new file:   "\346\225\260\346\215\256\351\252\214\350\257\201.sql"
2026-04-27 15:48:38 +08:00

777 lines
27 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- Databricks notebook source
---------------------------------------------在配置表中提取所需字段去重，并对编码类数据补位、公司名称简化、产品重新归属公司------------------------------------
insert overwrite table tmp.tmp_ext_xiehe_pack_property_tmp
----code 补码
---重新归属公司
select
distinct
--20250428 chenwu iqivacode扩到12位,brandcode 扩到9位
if( IQVIA_PACK_CODE REGEXP '^[0-9]',right(concat('000000000000',IQVIA_PACK_CODE),12),IQVIA_PACK_CODE) as IQVIA_PACK_CODE --right(concat('0000000', IQVIA_PACK_CODE),7)
,right(concat('000000000', IQVIA_PROD_CODE),9) as IQVIA_PROD_CODE --right(concat('00000', IQVIA_PROD_CODE),5)
,a.pack_des
,a.APP1_COD
,a.APP1_DES
,a.APP1_DES_C
,a.APP2_COD
,a.APP2_DES
,a.APP2_DES_C
,a.APP3_COD
,a.APP3_DES
,a.APP3_DES_C
,a.ATC1_COD
,a.ATC1_DES
,a.ATC1_DES_C
,a.ATC2_COD
,a.ATC2_DES
,a.ATC2_DES_C
,a.ATC3_COD
,a.ATC3_DES
,a.ATC3_DES_C
,a.ATC4_COD
,a.ATC4_DES
,a.ATC4_DES_C
,a.BIO_DESC
,right(concat('000000', a.CMPS_COD),6) AS CMPS_COD
,a.CMPS_DES
,a.CMPS_DES_C
,COALESCE(c.corp_cod,b.corp_cod,a.corp_cod) as corp_cod
,COALESCE(c.corp_des,b.corp_des,case when a.CORP_DES like '% GROUP%' then replace(a.CORP_DES,' GROUP','') else a.CORP_DES end) as CORP_DES
,COALESCE(d.corp_des_c,a.corp_des_c ) as corp_des_c
,a.EDL_DESC
,a.ETH_OTC_DESC
,a.GENE_ORIG_DESC
,a.GQCE_DESC
,COALESCE(c.manu_cod,b.manu_cod,a.manu_cod) manu_cod
,COALESCE(c.manu_des,b.manu_des,a.manu_des) manu_des
,COALESCE(e.manu_des_c,a.manu_des_c) manu_des_c
,case when a.MNFL_COD = 'L' then 'L' else 'M' end MNFL_COD --chpa逻辑
,case when a.MNFL_DES='Local' then 'LOCAL' else 'MNC' end MNFL_DES --chpa逻辑
-- ,a.MNFL_COD
-- ,a.MNFL_DES
,a.NRDL_DESC
,a.STGH_DES
,a.PACK_LCH
,a.PAED_DESC
,a.PROD_DES
,a.PROD_DES_C
,a.TCM_DESC
,CASE WHEN a.VBP_DESC IN ('VBP-IN','VBP-N/A') THEN 'VBP' ELSE 'Non VBP' END VBP_DESC --chpa逻辑
-- ,a.VBP_DESC
,a.Unit
,a.Counting_Unit
,a.Dosage_Unit
from dwd.dwd_gnd_xiehe_pack_info a
left join (select right(concat('000000000',prod_cod ),9) prod_cod,corp_cod
,case when CORP_DES like '% GROUP%' then replace(CORP_DES,' GROUP','') else CORP_DES end corp_des,right(concat('000000',manu_cod ),6) manu_cod,manu_des
from dwd.dwd_gnd_tbl_corp_change
where pack_cod is null
) b on right(concat('000000000', IQVIA_PROD_CODE),9)= b.prod_cod
--right(concat('00000',A.IQVIA_PROD_CODE ),5)= b.prod_cod
left join (select if( pack_cod REGEXP '^[0-9]',right(concat('000000000000',pack_cod),12),pack_cod) pack_cod,corp_cod
,case when CORP_DES like '% GROUP%' then replace(CORP_DES,' GROUP','') else CORP_DES end corp_des,right(concat('000000',manu_cod ),6) manu_cod,manu_des
from dwd.dwd_gnd_tbl_corp_change
where pack_cod is not null
) c on if( IQVIA_PACK_CODE REGEXP '^[0-9]',right(concat('000000000000',IQVIA_PACK_CODE),12),IQVIA_PACK_CODE) = c.pack_cod
--right(concat('0000000', IQVIA_PACK_CODE),7) = c.pack_cod
left join (select distinct corp_cod,corp_des_c from dwd.dwd_gnd_xiehe_pack_info) d on coalesce(C.corp_cod,B.corp_cod ) = d.corp_cod
Left join (select distinct right(concat('000000',manu_cod ),6) manu_cod,manu_des,manu_des_c from dwd.dwd_gnd_xiehe_pack_info) e On coalesce(C.manu_cod,B.manu_cod ) = e.manu_cod
;
--------关联tblmarket 数据
-----MARKET
--第一步：先处理 Extend_Market 和 NOT_IN_FLAG 都为空的数据，代表有明确定义得市场
insert overwrite table tmp.tmp_ext_xiehe_pack_property
select distinct
t2.market,t1.*,
t2.bu,CASE WHEN t2.extend_market_ratio IS NULL THEN '1' ELSE t2.extend_market_ratio END AS Market_Ratio
from tmp.tmp_ext_xiehe_pack_property_tmp t1
left join (select * from dwd.dwd_gnd_ims_tblmarket WHERE Extend_Market IS NULL AND ( NOT_IN_FLAG IS NULL or NOT_IN_FLAG = '1' )) t2
on t1.ATC1_COD = case when t2.ATC1_Code is null then t1.ATC1_COD else t2.ATC1_Code end
and t1.ATC2_COD = case when t2.ATC2_Code is null then t1.ATC2_COD else t2.ATC2_Code end
and t1.ATC3_COD = case when t2.ATC3_Code is null then t1.ATC3_COD else t2.ATC3_Code end
and t1.ATC4_COD = case when t2.ATC4_Code is null then t1.ATC4_COD else t2.ATC4_Code end
and t1.APP1_COD = case when t2.NFC1_Code is null then t1.APP1_COD else t2.NFC1_Code end
and t1.APP2_COD = case when t2.NFC2_Code is null then t1.APP2_COD else t2.NFC2_Code end
and t1.APP3_COD = case when t2.NFC3_Code is null then t1.APP3_COD else t2.NFC3_Code end
and t1.CORP_COD = case when t2.corporation_code is null then t1.CORP_COD else t2.corporation_code end
and t1.MANU_COD = case when t2.Manufacturer_Code is null then t1.MANU_COD else t2.Manufacturer_Code end
and right(concat('000000000', coalesce(t1.IQVIA_PROD_CODE,'')),9) = case when t2.Product_Code is null then right(concat('000000000', coalesce(t1.IQVIA_PROD_CODE,'')),9) else right(concat('000000000', coalesce(t2.Product_Code,'')),9) end
-- and t1.IQVIA_PACK_CODE = case when t2.Pack_Code is null then t1.IQVIA_PACK_CODE else t2.Pack_Code end
and nvl(t1.STGH_DES,'') = case when t2.Strength is null then nvl(t1.STGH_DES,'') else nvl(t2.Strength,'') end
and right(concat('000000', coalesce(t1.CMPS_COD,'')),6) = case when t2.Molecule_Code is null then right(concat('000000', coalesce(t1.CMPS_COD,'')),6) else right(concat('000000', coalesce( t2.Molecule_Code ,'')),6) end
and if( coalesce(t1.IQVIA_PACK_CODE,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t1.IQVIA_PACK_CODE,'')),12),coalesce(t1.IQVIA_PACK_CODE,''))
= case when t2.pack_code is null
then if( coalesce(t1.IQVIA_PACK_CODE,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t1.IQVIA_PACK_CODE,'')),12),coalesce(t1.IQVIA_PACK_CODE,''))
else if( coalesce(t2.pack_code,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t2.pack_code,'')),12),coalesce(t2.pack_code,'')) end
where t2.market is not null
;
--第二步：
--处理 Extend_Market 为空，并且 NOT_IN_FLAG 有值的数据，代表反选定义市场的规则
insert overwrite table tmp.tmp_ext_xiehe_pack_property_del
select distinct t2.market,t1.*,t2.bu,CASE WHEN t2.extend_market_ratio IS NULL THEN '1' ELSE t2.extend_market_ratio END AS Market_Ratio
from tmp.tmp_ext_xiehe_pack_property_tmp t1
left join (select * from dwd.dwd_gnd_ims_tblmarket WHERE Extend_Market IS NULL AND NOT_IN_FLAG = '0' ) t2
on t1.ATC1_COD = case when t2.ATC1_Code is null then t1.ATC1_COD else t2.ATC1_Code end
and t1.ATC2_COD = case when t2.ATC2_Code is null then t1.ATC2_COD else t2.ATC2_Code end
and t1.ATC3_COD = case when t2.ATC3_Code is null then t1.ATC3_COD else t2.ATC3_Code end
and t1.ATC4_COD = case when t2.ATC4_Code is null then t1.ATC4_COD else t2.ATC4_Code end
and t1.APP1_COD = case when t2.NFC1_Code is null then t1.APP1_COD else t2.NFC1_Code end
and t1.APP2_COD = case when t2.NFC2_Code is null then t1.APP2_COD else t2.NFC2_Code end
and t1.APP3_COD = case when t2.NFC3_Code is null then t1.APP3_COD else t2.NFC3_Code end
and t1.CORP_COD = case when t2.corporation_code is null then t1.CORP_COD else t2.corporation_code end
and t1.MANU_COD = case when t2.Manufacturer_Code is null then t1.MANU_COD else t2.Manufacturer_Code end
and right(concat('000000000', coalesce(t1.IQVIA_PROD_CODE,'')),9) = case when t2.Product_Code is null then right(concat('000000000', coalesce(t1.IQVIA_PROD_CODE,'')),9) else right(concat('000000000', coalesce(t2.Product_Code,'')),9) end
and nvl(t1.STGH_DES,'') = case when t2.Strength is null then nvl(t1.STGH_DES,'') else nvl(t2.Strength,'') end
and right(concat('000000', coalesce(t1.CMPS_COD,'')),6) = case when t2.Molecule_Code is null then right(concat('000000', coalesce(t1.CMPS_COD,'')),6) else right(concat('000000', coalesce( t2.Molecule_Code ,'')),6) end
and if( coalesce(t1.IQVIA_PACK_CODE,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t1.IQVIA_PACK_CODE,'')),12),coalesce(t1.IQVIA_PACK_CODE,''))
= case when t2.pack_code is null
then if( coalesce(t1.IQVIA_PACK_CODE,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t1.IQVIA_PACK_CODE,'')),12),coalesce(t1.IQVIA_PACK_CODE,''))
else if( coalesce(t2.pack_code,'') REGEXP '^[0-9]',right(concat('000000000000', coalesce(t2.pack_code,'')),12),coalesce(t2.pack_code,'')) end
where t2.market is not null
;
--！！！注意此段是反选的规则，故执行的是从已经定义好的TempMKT中删除数据
--需要看TempMKT的字段结构，没有值的字段，在Value里面放个空值
MERGE INTO tmp.tmp_ext_xiehe_pack_property AS t1
USING tmp.tmp_ext_xiehe_pack_property_del AS t2
on t1.ATC1_COD = t2.ATC1_COD
and t1.ATC2_COD = t2.ATC2_COD
and t1.ATC3_COD = t2.ATC3_COD
and t1.ATC4_COD = t2.ATC4_COD
and t1.APP1_COD = t2.APP1_COD
and t1.APP2_COD = t2.APP2_COD
and t1.APP3_COD = t2.APP3_COD
and t1.CORP_COD = t2.CORP_COD
and t1.MANU_COD = t2.MANU_COD
and t1.IQVIA_PROD_CODE = t2.IQVIA_PROD_CODE
and t1.IQVIA_PACK_CODE = t2.IQVIA_PACK_CODE
and t1.STGH_DES = t2.STGH_DES
and t1.CMPS_COD = t2.CMPS_COD
and t1.market = t2.market
WHEN MATCHED THEN DELETE
;
---第三步：
--处理 Extend_Market 有值得数据，用已有市场，定义新的市场，带上系数
insert into tmp.tmp_ext_xiehe_pack_property
select distinct
t2.Market,
t1.iqvia_pack_code ,
t1.iqvia_prod_code
,t1.pack_des
,t1.APP1_COD
,t1.APP1_DES
,t1.APP1_DES_C
,t1.APP2_COD
,t1.APP2_DES
,t1.APP2_DES_C
,t1.APP3_COD
,t1.APP3_DES
,t1.APP3_DES_C
,t1.ATC1_COD
,t1.ATC1_DES
,t1.ATC1_DES_C
,t1.ATC2_COD
,t1.ATC2_DES
,t1.ATC2_DES_C
,t1.ATC3_COD
,t1.ATC3_DES
,t1.ATC3_DES_C
,t1.ATC4_COD
,t1.ATC4_DES
,t1.ATC4_DES_C
,t1.BIO_DESC
,t1.CMPS_COD
,t1.CMPS_DES
,t1.CMPS_DES_C
,t1.corp_cod
,t1.CORP_DES
,t1.corp_des_c
,t1.EDL_DESC
,t1.ETH_OTC_DESC
,t1.GENE_ORIG_DESC
,t1.GQCE_DESC
,t1.manu_cod
,t1.manu_des
,t1.manu_des_c
,t1.MNFL_COD
,t1.MNFL_DES
,t1.NRDL_DESC
,t1.STGH_DES
,t1.PACK_LCH
,t1.PAED_DESC
,t1.PROD_DES
,t1.PROD_DES_C
,t1.TCM_DESC
,t1.VBP_DESC
,t1.Unit
,t1.Counting_Unit
,t1.Dosage_Unit
,t2.BU
,case when t2.Extend_Market_Ratio is null then '1' end AS Market_Ratio --市场的系数若没有维护，默认为1
from tmp.tmp_ext_xiehe_pack_property t1
left join (select Market, BU,Extend_Market,Extend_Market_Ratio from dwd.dwd_gnd_ims_tblmarket WHERE Extend_Market IS NOT NULL) t2 on t1.Market=t2.Extend_Market
where t2.Market is not null
;
-- COMMAND ----------
--补上未匹配到Market的pack
insert into tmp.tmp_ext_xiehe_pack_property
(
Market,
IQVIA_PACK_CODE,
IQVIA_PROD_CODE,
pack_des,
APP1_COD,
APP1_DES,
APP1_DES_C,
APP2_COD,
APP2_DES,
APP2_DES_C,
APP3_COD,
APP3_DES,
APP3_DES_C,
ATC1_COD,
ATC1_DES,
ATC1_DES_C,
ATC2_COD,
ATC2_DES,
ATC2_DES_C,
ATC3_COD,
ATC3_DES,
ATC3_DES_C,
ATC4_COD,
ATC4_DES,
ATC4_DES_C,
BIO_DESC,
CMPS_COD,
CMPS_DES,
CMPS_DES_C,
corp_cod,
CORP_DES,
corp_des_c,
EDL_DESC,
ETH_OTC_DESC,
GENE_ORIG_DESC,
GQCE_DESC,
manu_cod,
manu_des,
manu_des_c,
MNFL_COD,
MNFL_DES,
NRDL_DESC,
STGH_DES,
PACK_LCH,
PAED_DESC,
PROD_DES,
PROD_DES_C,
TCM_DESC,
VBP_DESC,
Unit,
Counting_Unit,
Dosage_Unit,
bu,
Market_Ratio
)
select distinct
'Non Market' as Market,
t1.IQVIA_PACK_CODE,
t1.IQVIA_PROD_CODE,
t1.pack_des,
t1.APP1_COD,
t1.APP1_DES,
t1.APP1_DES_C,
t1.APP2_COD,
t1.APP2_DES,
t1.APP2_DES_C,
t1.APP3_COD,
t1.APP3_DES,
t1.APP3_DES_C,
t1.ATC1_COD,
t1.ATC1_DES,
t1.ATC1_DES_C,
t1.ATC2_COD,
t1.ATC2_DES,
t1.ATC2_DES_C,
t1.ATC3_COD,
t1.ATC3_DES,
t1.ATC3_DES_C,
t1.ATC4_COD,
t1.ATC4_DES,
t1.ATC4_DES_C,
t1.BIO_DESC,
t1.CMPS_COD,
t1.CMPS_DES,
t1.CMPS_DES_C,
t1.corp_cod,
t1.CORP_DES,
t1.corp_des_c,
t1.EDL_DESC,
t1.ETH_OTC_DESC,
t1.GENE_ORIG_DESC,
t1.GQCE_DESC,
t1.manu_cod,
t1.manu_des,
t1.manu_des_c,
t1.MNFL_COD,
t1.MNFL_DES,
t1.NRDL_DESC,
t1.STGH_DES,
t1.PACK_LCH,
t1.PAED_DESC,
t1.PROD_DES,
t1.PROD_DES_C,
t1.TCM_DESC,
t1.VBP_DESC,
t1.Unit,
t1.Counting_Unit,
t1.Dosage_Unit,
'' as bu,
'1' as Market_Ratio
from tmp.tmp_ext_xiehe_pack_property_tmp t1
left join tmp.tmp_ext_xiehe_pack_property t2
on t1.IQVIA_PACK_CODE = t2.IQVIA_PACK_CODE
where t2.IQVIA_PACK_CODE is null
-- COMMAND ----------
----------------------------------------------------补齐维度信息---------------------------
------对属于az 的产品打上 is az 标识
insert overwrite table dws.dws_ext_xiehe_pack_property
select
concat(t1.Market,'_',T1.IQVIA_PACK_CODE) as MARKET_PACK_KEY
,t1.market
,t1.IQVIA_PACK_CODE
,t1.IQVIA_PROD_CODE
,t1.pack_des
,t1.APP1_COD
,t1.APP1_DES
,t1.APP1_DES_C
,t1.APP2_COD
,t1.APP2_DES
,t1.APP2_DES_C
,t1.APP3_COD
,t1.APP3_DES
,t1.APP3_DES_C
,t1.ATC1_COD
,t1.ATC1_DES
,t1.ATC1_DES_C
,t1.ATC2_COD
,t1.ATC2_DES
,t1.ATC2_DES_C
,t1.ATC3_COD
,t1.ATC3_DES
,t1.ATC3_DES_C
,t1.ATC4_COD
,t1.ATC4_DES
,t1.ATC4_DES_C
,t1.BIO_DESC
,t1.CMPS_COD
,t1.CMPS_DES
,t1.CMPS_DES_C
,t1.corp_cod
,t1.CORP_DES
,t1.corp_des_c
,t1.EDL_DESC
,t1.ETH_OTC_DESC
,t1.GENE_ORIG_DESC
,t1.GQCE_DESC
,t1.manu_cod
,t1.manu_des
,t1.manu_des_c
,t1.MNFL_COD
,t1.MNFL_DES
,t1.NRDL_DESC
,t1.STGH_DES
,t1.PACK_LCH
,t1.PAED_DESC
,t1.PROD_DES
,t1.PROD_DES_C
,t1.TCM_DESC
,t1.VBP_DESC
,t1.Unit
,t1.Counting_Unit
,t1.Dosage_Unit
,t1.bu
,t1.Market_Ratio
,NRDL_ENTRY_DATE
,case when ta.prod_code is null then 'N' ELSE 'Y' END IS_AZ
,null as AZ_MAIN
,BRANDTYPE.brand_type AS BRANDTYPE
from tmp.tmp_ext_xiehe_pack_property t1
left join (select distinct Pack_Code,NRDL_ENTRY_DATE from dwd.dwd_ims_td_pack_additional_attribute ) t2 on t1.IQVIA_PACK_CODE =t2.Pack_Code
left join (select distinct IQVIA_PROD_CODE prod_code from tmp.tmp_ext_xiehe_pack_property where corp_cod='A5Z' ) ta on t1.IQVIA_PROD_CODE=ta.prod_code
LEFT JOIN dwd.dwd_gnd_ims_tblbrandtype BRANDTYPE ON t1.IQVIA_PACK_CODE = BRANDTYPE.PACK_COD
;
-- COMMAND ----------
----------------------------------------------------补齐维度信息---------------------------
insert overwrite table tmp.tmp_dm_ext_xiehe_pack_property
select
t1.MARKET_PACK_KEY
,t1.market
,t1.IQVIA_PACK_CODE
,t1.IQVIA_PROD_CODE
,t1.pack_des
,t1.APP1_COD
,t1.APP1_DES
,t1.APP1_DES_C
,t1.APP2_COD
,t1.APP2_DES
,t1.APP2_DES_C
,t1.APP3_COD
,t1.APP3_DES
,t1.APP3_DES_C
,t1.ATC1_COD
,t1.ATC1_DES
,t1.ATC1_DES_C
,t1.ATC2_COD
,t1.ATC2_DES
,t1.ATC2_DES_C
,t1.ATC3_COD
,t1.ATC3_DES
,t1.ATC3_DES_C
,t1.ATC4_COD
,t1.ATC4_DES
,t1.ATC4_DES_C
,t1.BIO_DESC
,t1.CMPS_COD
,t1.CMPS_DES
,t1.CMPS_DES_C
,t1.corp_cod
,t1.CORP_DES
,t1.corp_des_c
,t1.EDL_DESC
,t1.ETH_OTC_DESC
,t1.GENE_ORIG_DESC
,t1.GQCE_DESC
,t1.manu_cod
,t1.manu_des
,t1.manu_des_c
,t1.MNFL_COD
,t1.MNFL_DES
,t1.NRDL_DESC
,t1.STGH_DES
,t1.PACK_LCH
,t1.PAED_DESC
,t1.PROD_DES
,t1.PROD_DES_C
,t1.TCM_DESC
,t1.VBP_DESC
,t1.Unit
,t1.Counting_Unit
,t1.Dosage_Unit
,t1.bu
,t1.Market_Ratio
,t1.NRDL_ENTRY_DATE
,t1.IS_AZ
,t1.AZ_MAIN
,t1.BRANDTYPE
,nvl(cla.class,'Others') class
,ta.ta
from dws.dws_ext_xiehe_pack_property t1
left join dwd.dwd_gnd_ext_retail_dim_ta ta on t1.market = ta.market
left join dwd.dwd_gnd_tblclass cla on t1.MARKET = case when cla.market is null then t1.market else cla.market end
and nvl(t1.iqvia_pack_code,'') = case when cla.pack_code is null then nvl(t1.iqvia_pack_code,'')
else if( cla.pack_code REGEXP '^[0-9]',right(concat('000000000000',cla.pack_code),12),cla.pack_code) end --right(concat('0000000',cla.pack_code ), 7)
and nvl(t1.IQVIA_PROD_CODE,'') = case when cla.product_code is null then nvl(t1.IQVIA_PROD_CODE ,'')
else right(concat('000000000',cla.product_code ),9 ) end --right(concat('00000',cla.product_code ),5 )
and nvl(t1.CMPS_COD,'') = case when cla.molecule_code is null then nvl(t1.cmps_cod ,'') else right(concat('000000', cla.molecule_code),6 ) end
and nvl(t1.corp_cod,'') = case when cla.Corporation_code is null then nvl(t1.corp_cod ,'') else cla.Corporation_code end
and nvl(t1.manu_cod,'')= case when cla.Manufacturer_Code is null then nvl(t1.manu_cod,'') else cla.Manufacturer_Code end
and nvl(t1.atc1_cod,'')= case when cla.ATC1_Code is null then nvl(t1.atc1_cod ,'') else cla.ATC1_Code end
and nvl(t1.ATC2_COD,'') = case when cla.ATC2_Code is null then nvl(t1.ATC2_COD ,'') else cla.ATC2_Code end
and nvl(t1.ATC3_COD,'') = case when cla.ATC3_Code is null then nvl(t1.ATC3_COD ,'') else cla.ATC3_Code end
and nvl(t1.ATC4_COD,'') = case when cla.ATC4_Code is null then nvl(t1.ATC4_COD ,'') else cla.ATC4_Code end
and nvl(t1.app1_cod,'') = case when cla.NFC1_Code is null then nvl(t1.app1_cod ,'') else cla.NFC1_Code end
and nvl(t1.APP2_COD,'') = case when cla.NFC2_Code is null then nvl(t1.APP2_COD ,'') else cla.NFC2_Code end
and nvl(t1.APP3_COD,'') = case when cla.NFC3_Code is null then nvl(t1.APP3_COD ,'') else cla.NFC3_Code end
and nvl(t1.stgh_des,'') = case when cla.Strength is null then nvl(t1.stgh_des ,'') else cla.Strength end
;
-- COMMAND ----------
with ims_pack_property as (
select
MARKET_PACK_KEY,
max(PACK_COD) as PACK_COD,
max(PACK_DES) as PACK_DES,
max(STGH_DES) as STGH_DES,
max(PACK_LCH) as PACK_LCH,
max(Family_Code) as Family_Code,
max(Family_Name) as Family_Name,
max(PROD_COD) as PROD_COD,
max(PROD_DES) as PROD_DES,
max(PROD_DES_C) as PROD_DES_C,
max(CMPS_COD) as CMPS_COD,
max(CMPS_DES) as CMPS_DES,
max(CMPS_DES_C) as CMPS_DES_C,
max(ATC1_COD) as ATC1_COD,
max(ATC2_COD) as ATC2_COD,
max(ATC3_COD) as ATC3_COD,
max(ATC4_COD) as ATC4_COD,
max(APP1_COD) as APP1_COD,
max(APP2_COD) as APP2_COD,
max(APP3_COD) as APP3_COD,
max(BIO_DESC) as BIO_DESC,
max(GENE_ORIG_DESC) as GENE_ORIG_DESC,
max(ETH_OTC_DESC) as ETH_OTC_DESC,
max(NRDL_DESC) as NRDL_DESC,
max(NRDL_Entry_Date) as NRDL_Entry_Date,
max(EDL_DESC) as EDL_DESC,
max(TCM_DESC) as TCM_DESC,
max(PAED_DESC) as PAED_DESC,
max(GQCE_DESC) as GQCE_DESC,
max(VBP_DESC) as VBP_DESC,
max(MANU_COD) as MANU_COD,
max(MANU_DES) as MANU_DES,
max(MANU_DES_C) as MANU_DES_C,
max(MNFL_COD) as MNFL_COD,
max(MNFL_DES) as MNFL_DES,
max(CORP_COD) as CORP_COD,
max(CORP_DES) as CORP_DES,
max(CORP_DES_C) as CORP_DES_C,
max(BrandType) as BrandType,
max(MARKET) as MARKET,
max(KEY_COMPETITOR) as KEY_COMPETITOR,
max(IS_AZ) as IS_AZ,
max(AZ_MAIN) as AZ_MAIN,
max(AZ_Related) as AZ_Related,
max(atc1_des) as atc1_des,
max(atc1_des_c) as atc1_des_c,
max(atc2_des) as atc2_des,
max(atc2_des_c) as atc2_des_c,
max(atc3_des) as atc3_des,
max(atc3_des_c) as atc3_des_c,
max(atc4_des) as atc4_des,
max(atc4_des_c) as atc4_des_c,
max(app1_des) as app1_des,
max(app1_des_c) as app1_des_c,
max(app2_des) as app2_des,
max(app2_des_c) as app2_des_c,
max(app3_des) as app3_des,
max(app3_des_c) as app3_des_c,
max(Class) as Class
from dm.dm_ims_td_pack_property
group by MARKET_PACK_KEY
)
insert overwrite table dm.dm_ext_xiehe_pack_property (
MARKET_PACK_KEY,
PACK_COD,
PACK_DES,
STGH_DES,
PACK_LCH,
Family_Code,
Family_Name,
PROD_COD,
PROD_DES,
PROD_DES_C,
CMPS_COD,
CMPS_DES,
CMPS_DES_C,
ATC1_COD,
ATC2_COD,
ATC3_COD,
ATC4_COD,
APP1_COD,
APP2_COD,
APP3_COD,
BIO_DESC,
GENE_ORIG_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_Entry_Date,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
VBP_DESC,
MANU_COD,
MANU_DES,
MANU_DES_C,
MNFL_COD,
MNFL_DES,
CORP_COD,
CORP_DES,
CORP_DES_C,
BrandType,
MARKET,
KEY_COMPETITOR,
IS_AZ,
AZ_MAIN,
AZ_Related,
atc1_des,
atc1_des_c,
atc2_des,
atc2_des_c,
atc3_des,
atc3_des_c,
atc4_des,
atc4_des_c,
app1_des,
app1_des_c,
app2_des,
app2_des_c,
app3_des,
app3_des_c,
Class,
ETL_INSERT_DT,
ETL_UPDATE_DT,
Market_Ratio,
counting_unit,
bu,
Dosage_Unit,
ta
)
select distinct
t1.market_pack_key,
t1.iqvia_pack_code as PACK_COD,
coalesce(t2.PACK_DES,t1.pack_des) as PACK_DES,
coalesce(t2.STGH_DES,t1.stgh_des) as STGH_DES,
coalesce(t2.PACK_LCH,t1.pack_lch) as PACK_LCH,
t2.Family_Code,
t2.Family_Name,
coalesce(t2.PROD_COD,t1.iqvia_prod_code) as PROD_COD,
coalesce(t2.PROD_DES,t1.prod_des) as PROD_DES,
coalesce(t2.PROD_DES_C,t1.prod_des_c) as PROD_DES_C,
coalesce(t2.CMPS_COD,t1.cmps_cod) as CMPS_COD,
coalesce(t2.CMPS_DES,t1.cmps_des) as CMPS_DES,
coalesce(t2.CMPS_DES_C,t1.cmps_des_c) as CMPS_DES_C,
coalesce(t2.ATC1_COD,t1.atc1_cod) as ATC1_COD,
coalesce(t2.ATC2_COD,t1.atc2_cod) as ATC2_COD,
coalesce(t2.ATC3_COD,t1.atc3_cod) as ATC3_COD,
coalesce(t2.ATC4_COD,t1.atc4_cod) as ATC4_COD,
coalesce(t2.APP1_COD,t1.app1_cod) as APP1_COD,
coalesce(t2.APP2_COD,t1.app2_cod) as APP2_COD,
coalesce(t2.APP3_COD,t1.app3_cod) as APP3_COD,
coalesce(t2.BIO_DESC,t1.bio_desc) as BIO_DESC,
coalesce(t2.GENE_ORIG_DESC,t1.gene_orig_desc) as GENE_ORIG_DESC,
coalesce(t2.ETH_OTC_DESC,t1.eth_otc_desc) as ETH_OTC_DESC,
coalesce(t2.NRDL_DESC,t1.nrdl_desc) as NRDL_DESC,
coalesce(t2.NRDL_Entry_Date,t1.NRDL_Entry_Date) as NRDL_Entry_Date,
coalesce(t2.EDL_DESC,t1.edl_desc) as EDL_DESC,
coalesce(t2.TCM_DESC,t1.tcm_desc) as TCM_DESC,
coalesce(t2.PAED_DESC,t1.paed_desc) as PAED_DESC,
coalesce(t2.GQCE_DESC,t1.gqce_desc) as GQCE_DESC,
coalesce(t2.VBP_DESC,t1.vbp_desc) as VBP_DESC,
coalesce(t2.MANU_COD,t1.manu_cod) as MANU_COD,
coalesce(t2.MANU_DES,t1.manu_des) as MANU_DES,
coalesce(t2.MANU_DES_C,t1.manu_des_c) as MANU_DES_C,
coalesce(t2.MNFL_COD,t1.mnfl_cod) as MNFL_COD,
coalesce(t2.MNFL_DES,t1.mnfl_des) as MNFL_DES,
coalesce(t2.CORP_COD,t1.corp_cod) as CORP_COD,
coalesce(t2.CORP_DES,t1.corp_des) as CORP_DES,
coalesce(t2.CORP_DES_C,t1.corp_des_c) as CORP_DES_C,
coalesce(t2.BrandType,t1.BrandType) as BrandType,
t1.market,
t2.KEY_COMPETITOR,
coalesce(t2.IS_AZ,t1.IS_AZ) as IS_AZ,
coalesce(t2.AZ_MAIN,t1.AZ_MAIN) as AZ_MAIN,
t2.AZ_Related,
coalesce(t2.atc1_des,t1.atc1_des) as atc1_des,
coalesce(t2.atc1_des_c,t1.atc1_des_c) as atc1_des_c,
coalesce(t2.atc2_des,t1.atc2_des) as atc2_des,
coalesce(t2.atc2_des_c,t1.atc2_des_c) as atc2_des_c,
coalesce(t2.atc3_des,t1.atc3_des) as atc3_des,
coalesce(t2.atc3_des_c,t1.atc3_des_c) as atc3_des_c,
coalesce(t2.atc4_des,t1.atc4_des) as atc4_des,
coalesce(t2.atc4_des_c,t1.atc4_des_c) as atc4_des_c,
coalesce(t2.app1_des,t1.app1_des) as app1_des,
coalesce(t2.app1_des_c,t1.app1_des_c) as app1_des_c,
coalesce(t2.app2_des,t1.app2_des) as app2_des,
coalesce(t2.app2_des_c,t1.app2_des_c) as app2_des_c,
coalesce(t2.app3_des,t1.app3_des) as app3_des,
coalesce(t2.app3_des_c,t1.app3_des_c) as app3_des_c,
coalesce(t2.Class,t1.Class) as Class,
from_utc_timestamp(current_timestamp(),'UTC+8') as ETL_INSERT_DT,
from_utc_timestamp(current_timestamp(),'UTC+8') as ETL_UPDATE_DT,
cast(t1.market_ratio as decimal(30,10)) as market_ratio,
coalesce(cast(t1.counting_unit / coalesce(t1.unit,1) as decimal(30,10)),1) as counting_unit,
t1.bu,
t1.Dosage_Unit,
t1.ta
from tmp.tmp_dm_ext_xiehe_pack_property t1
left join ims_pack_property t2
on t1.market_pack_key = t2.market_pack_key
where t1.market <> 'Non Market'
union
select distinct
concat('XIEHE ALL Market_',t1.iqvia_pack_code) as market_pack_key,
t1.iqvia_pack_code as PACK_COD,
max(coalesce(t2.PACK_DES,t1.pack_des)) as PACK_DES,
max(coalesce(t2.STGH_DES,t1.stgh_des)) as STGH_DES,
max(coalesce(t2.PACK_LCH,t1.pack_lch)) as PACK_LCH,
max(t2.Family_Code) as Family_Code,
max(t2.Family_Name) as Family_Name,
max(coalesce(t2.PROD_COD,t1.iqvia_prod_code)) as PROD_COD,
max(coalesce(t2.PROD_DES,t1.prod_des)) as PROD_DES,
max(coalesce(t2.PROD_DES_C,t1.prod_des_c)) as PROD_DES_C,
max(coalesce(t2.CMPS_COD,t1.cmps_cod)) as CMPS_COD,
max(coalesce(t2.CMPS_DES,t1.cmps_des)) as CMPS_DES,
max(coalesce(t2.CMPS_DES_C,t1.cmps_des_c)) as CMPS_DES_C,
max(coalesce(t2.ATC1_COD,t1.atc1_cod)) as ATC1_COD,
max(coalesce(t2.ATC2_COD,t1.atc2_cod)) as ATC2_COD,
max(coalesce(t2.ATC3_COD,t1.atc3_cod)) as ATC3_COD,
max(coalesce(t2.ATC4_COD,t1.atc4_cod)) as ATC4_COD,
max(coalesce(t2.APP1_COD,t1.app1_cod)) as APP1_COD,
max(coalesce(t2.APP2_COD,t1.app2_cod)) as APP2_COD,
max(coalesce(t2.APP3_COD,t1.app3_cod)) as APP3_COD,
max(coalesce(t2.BIO_DESC,t1.bio_desc)) as BIO_DESC,
max(coalesce(t2.GENE_ORIG_DESC,t1.gene_orig_desc)) as GENE_ORIG_DESC,
max(coalesce(t2.ETH_OTC_DESC,t1.eth_otc_desc)) as ETH_OTC_DESC,
max(coalesce(t2.NRDL_DESC,t1.nrdl_desc)) as NRDL_DESC,
max(coalesce(t2.NRDL_Entry_Date,t1.NRDL_Entry_Date)) as NRDL_Entry_Date,
max(coalesce(t2.EDL_DESC,t1.edl_desc)) as EDL_DESC,
max(coalesce(t2.TCM_DESC,t1.tcm_desc)) as TCM_DESC,
max(coalesce(t2.PAED_DESC,t1.paed_desc)) as PAED_DESC,
max(coalesce(t2.GQCE_DESC,t1.gqce_desc)) as GQCE_DESC,
max(coalesce(t2.VBP_DESC,t1.vbp_desc)) as VBP_DESC,
max(coalesce(t2.MANU_COD,t1.manu_cod)) as MANU_COD,
max(coalesce(t2.MANU_DES,t1.manu_des)) as MANU_DES,
max(coalesce(t2.MANU_DES_C,t1.manu_des_c)) as MANU_DES_C,
max(coalesce(t2.MNFL_COD,t1.mnfl_cod)) as MNFL_COD,
max(coalesce(t2.MNFL_DES,t1.mnfl_des)) as MNFL_DES,
max(coalesce(t2.CORP_COD,t1.corp_cod)) as CORP_COD,
max(coalesce(t2.CORP_DES,t1.corp_des)) as CORP_DES,
max(coalesce(t2.CORP_DES_C,t1.corp_des_c)) as CORP_DES_C,
max(coalesce(t2.BrandType,t1.BrandType)) as BrandType,
'XIEHE ALL Market' as MARKET,
max(t2.KEY_COMPETITOR) as KEY_COMPETITOR,
max(coalesce(t2.IS_AZ,t1.IS_AZ)) as IS_AZ,
max(coalesce(t2.AZ_MAIN,t1.AZ_MAIN)) as AZ_MAIN,
max(t2.AZ_Related) as AZ_Related,
max(coalesce(t2.atc1_des,t1.atc1_des)) as atc1_des,
max(coalesce(t2.atc1_des_c,t1.atc1_des_c)) as atc1_des_c,
max(coalesce(t2.atc2_des,t1.atc2_des)) as atc2_des,
max(coalesce(t2.atc2_des_c,t1.atc2_des_c)) as atc2_des_c,
max(coalesce(t2.atc3_des,t1.atc3_des)) as atc3_des,
max(coalesce(t2.atc3_des_c,t1.atc3_des_c)) as atc3_des_c,
max(coalesce(t2.atc4_des,t1.atc4_des)) as atc4_des,
max(coalesce(t2.atc4_des_c,t1.atc4_des_c)) as atc4_des_c,
max(coalesce(t2.app1_des,t1.app1_des)) as app1_des,
max(coalesce(t2.app1_des_c,t1.app1_des_c)) as app1_des_c,
max(coalesce(t2.app2_des,t1.app2_des)) as app2_des,
max(coalesce(t2.app2_des_c,t1.app2_des_c)) as app2_des_c,
max(coalesce(t2.app3_des,t1.app3_des)) as app3_des,
max(coalesce(t2.app3_des_c,t1.app3_des_c)) as app3_des_c,
max(coalesce(t2.Class,t1.Class)) as Class,
from_utc_timestamp(current_timestamp(),'UTC+8') as ETL_INSERT_DT,
from_utc_timestamp(current_timestamp(),'UTC+8') as ETL_UPDATE_DT,
max(cast(t1.market_ratio as decimal(30,10))) as market_ratio,
coalesce(max(cast(t1.counting_unit / coalesce(t1.unit,1) as decimal(30,10))),1) as counting_unit,
max(t1.bu) as bu,
max(t1.Dosage_Unit) as Dosage_Unit,
max(t1.ta) as ta
from tmp.tmp_dm_ext_xiehe_pack_property t1
left join ims_pack_property t2
on t1.market_pack_key = t2.market_pack_key
group by t1.iqvia_pack_code
Reference in New Issue View Git Blame Copy Permalink