table Fact_Sales lineageTag: c6bdcb23-6f0c-4bd2-98b6-b3fd012d5ba6 column YYYYMM dataType: string lineageTag: 23a682d7-10d2-408f-9865-95266f1a1ce3 summarizeBy: none sourceColumn: YYYYMM annotation SummarizationSetBy = Automatic column AUDIT_COD dataType: string lineageTag: beb744f3-a579-412a-bea8-ab630611d346 summarizeBy: none sourceColumn: AUDIT_COD annotation SummarizationSetBy = Automatic column PACK_COD dataType: string lineageTag: b8a924a3-e6db-4466-8d17-5bafeec347d1 summarizeBy: none sourceColumn: PACK_COD annotation SummarizationSetBy = Automatic column SALES_UNIT_CAL dataType: double lineageTag: 4c191e4c-b8cb-4d39-8065-fdad386c287b summarizeBy: sum sourceColumn: SALES_UNIT_CAL annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column SALES_UNIT_CAL_LY dataType: double lineageTag: 5c65cb87-1f7f-4c33-aa6f-da97d34a103d summarizeBy: sum sourceColumn: SALES_UNIT_CAL_LY annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column SALES_VALUE_CAL dataType: double lineageTag: 939a3931-54b5-4324-a85c-52bee74d988e summarizeBy: sum sourceColumn: SALES_VALUE_CAL annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column SALES_VALUE_CAL_LY dataType: double lineageTag: cea5d4e3-5f3a-492b-bd17-57003088499b summarizeBy: sum sourceColumn: SALES_VALUE_CAL_LY annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column CONUTING_UNIT dataType: double lineageTag: 787a5cf6-289e-49b7-bcfd-8852344c16f5 summarizeBy: sum sourceColumn: CONUTING_UNIT annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column CONUTING_UNIT_LY dataType: double lineageTag: 51fe3ca5-db57-4a35-b743-7396f22bcb47 summarizeBy: sum sourceColumn: CONUTING_UNIT_LY annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column DATA_SOURCE dataType: string lineageTag: a22e0dd0-28e2-4c4e-9a09-ffdd0fe6e5a8 summarizeBy: none sourceColumn: DATA_SOURCE annotation SummarizationSetBy = Automatic column DATE_KEY = DATE(LEFT([YYYYMM],4),RIGHT([YYYYMM],2),"01") formatString: General Date lineageTag: e245e59a-d641-4989-9fe8-5085167166c9 summarizeBy: none annotation SummarizationSetBy = Automatic column AUDIT_SOURCE = [AUDIT_COD] & [DATA_SOURCE] lineageTag: f57ad720-e851-467b-8977-2d94175446c5 summarizeBy: none annotation SummarizationSetBy = Automatic column CORP_COD dataType: string lineageTag: 74b507c4-e474-44e5-bb48-0ad38c5d6dd0 summarizeBy: none sourceColumn: CORP_COD annotation SummarizationSetBy = Automatic column AUDIT_KEY = [AUDIT_COD] & [DATA_SOURCE] & [REGION_TYPE] lineageTag: 2d21eb29-98a2-4359-abee-e8a0ee6ac982 summarizeBy: none annotation SummarizationSetBy = Automatic column REGION_TYPE dataType: string lineageTag: 359fd39c-3483-4b5e-8499-122eeb4384b6 summarizeBy: none sourceColumn: REGION_TYPE annotation SummarizationSetBy = Automatic column PLATFORM_TYPE dataType: string lineageTag: 7b863d28-ca16-425f-ac26-226896232a93 summarizeBy: none sourceColumn: PLATFORM_TYPE annotation SummarizationSetBy = Automatic column STORE_NAME dataType: string lineageTag: 5564f599-7429-4168-8a17-dee7c12f5455 summarizeBy: none sourceColumn: STORE_NAME annotation SummarizationSetBy = Automatic column STORE_TYPE dataType: string lineageTag: fa4ec6e7-6226-41f5-b3b9-f3b8725814af summarizeBy: none sourceColumn: STORE_TYPE annotation SummarizationSetBy = Automatic column PACK_FLAG dataType: int64 formatString: 0 lineageTag: ff6e8255-fd6d-44fb-ad51-53c990a37dee summarizeBy: sum sourceColumn: PACK_FLAG annotation SummarizationSetBy = Automatic column PROD_FLAG dataType: int64 formatString: 0 lineageTag: 5680c42d-22f3-434e-8e00-37d3a9e25c6e summarizeBy: sum sourceColumn: PROD_FLAG annotation SummarizationSetBy = Automatic column DTP_FLAG dataType: int64 formatString: 0 lineageTag: 4893181d-0fde-4860-84c6-59d600239976 summarizeBy: sum sourceColumn: DTP_FLAG annotation SummarizationSetBy = Automatic column COUNTY_FLAG = IF([REGION_TYPE] IN {"BBU_BU COUNTY","OBU_BU COUNTY","OBU COUNTY","BBU COUNTY"},1,0) formatString: 0 lineageTag: 54d2f08a-99ae-4b56-8612-73a265a98b88 summarizeBy: sum annotation SummarizationSetBy = Automatic column GEO_FLAG = SWITCH(TRUE(), [DTP_FLAG]=1 && [PROD_FLAG]=2 &&[PACK_FLAG]=1,2, [PROD_FLAG] = 1,1, [PACK_FLAG] = 1,1, 0) formatString: 0 lineageTag: f0871916-f337-41e8-8bd1-09493b73c7cf summarizeBy: sum annotation SummarizationSetBy = Automatic column 'new store type' = SWITCH(Fact_Sales[STORE_TYPE],"第三方","POP","品牌店","SO","平台店","SO","自营旗舰","SO",Fact_Sales[STORE_TYPE]) lineageTag: 7367a83f-3785-41a0-84ad-8a9c03c3d17b summarizeBy: none annotation SummarizationSetBy = Automatic column PACK_SOURCE = [PACK_COD] & [DATA_SOURCE] &[NEW_CODE] lineageTag: 579c6a09-6e92-49f4-a2f9-dd71c86aef6a summarizeBy: none annotation SummarizationSetBy = Automatic column STORE_TYPE_SOURCE = [STORE_TYPE] & [DATA_SOURCE] lineageTag: 1152f877-9d11-4ad8-a4e9-e56b2d9ae477 summarizeBy: none annotation SummarizationSetBy = Automatic column PLATFORM_TYPE_SOURCE = [PLATFORM_TYPE] & [DATA_SOURCE] lineageTag: 3799fa82-b187-48de-9085-e7eb56137e2d summarizeBy: none annotation SummarizationSetBy = Automatic column STORE_PLATFORM = [STORE_TYPE] & " - " & [PLATFORM_TYPE] lineageTag: 8b9050d4-c7f6-4a0e-9d00-1aff75be6332 summarizeBy: none annotation SummarizationSetBy = Automatic column CMPS_FLAG dataType: int64 formatString: 0 lineageTag: 39bd1fb6-6b57-4441-86fb-1158b6cc3335 summarizeBy: sum sourceColumn: CMPS_FLAG annotation SummarizationSetBy = Automatic column PRESCRIPTION dataType: double lineageTag: 7593dd08-7505-488d-88b8-8846d92930cc summarizeBy: sum sourceColumn: PRESCRIPTION annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column PRESCRIPTION_LY dataType: double lineageTag: 6cfbf76b-2417-4aa4-b12c-320e847a799b summarizeBy: sum sourceColumn: PRESCRIPTION_LY annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column DEPT_SOURCE = [dept_name] & [DATA_SOURCE] lineageTag: d633ff86-610c-4194-9637-0943ed386d75 summarizeBy: none annotation SummarizationSetBy = Automatic column DEPT_NAME dataType: string lineageTag: 55ee1c72-c8d5-4bfc-8e89-bfec08ada73c summarizeBy: none sourceColumn: DEPT_NAME annotation SummarizationSetBy = Automatic column NEW_CODE dataType: string lineageTag: b5f41ab8-591f-4c00-be44-65868a16e880 summarizeBy: none sourceColumn: NEW_CODE annotation SummarizationSetBy = Automatic column INST_CODE dataType: string lineageTag: 028d1aa1-41be-43d2-9fa0-977259a3c58f summarizeBy: none sourceColumn: INST_CODE annotation SummarizationSetBy = Automatic column AIA_HP_FLAG dataType: string lineageTag: 75af6441-c40e-4087-8a38-f72489993622 summarizeBy: none sourceColumn: AIA_HP_FLAG annotation SummarizationSetBy = Automatic column HP_SOURCE = [AIA_HP_FLAG] & [DATA_SOURCE] lineageTag: 70317408-2faf-4425-a64c-912904705571 summarizeBy: none annotation SummarizationSetBy = Automatic column INST_SOURCE = [INST_CODE] & [DATA_SOURCE] lineageTag: a4bfd8bf-f30f-4d48-9d28-6e3d6fb2023f summarizeBy: none annotation SummarizationSetBy = Automatic column H_LEVEL dataType: double lineageTag: 93973569-c447-41e6-b741-672a6539d97f summarizeBy: sum sourceColumn: H_LEVEL annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column REIMBURSE dataType: string lineageTag: 431ab1f8-1756-4589-9bd4-73ad3cd621bd summarizeBy: none sourceColumn: REIMBURSE annotation SummarizationSetBy = Automatic column REIMBURSE_TYPE dataType: string lineageTag: e0d76560-fc80-4d10-bd85-2ca6e37ae960 summarizeBy: none sourceColumn: REIMBURSE_TYPE annotation SummarizationSetBy = Automatic column PRESCRIPTION_SOURCE = [PRESCRIPTION_TYPE] & [DATA_SOURCE] lineageTag: f7a5c002-152a-4f10-b177-ef6b5cc21719 summarizeBy: none annotation SummarizationSetBy = Automatic column LEVEL_SOURCE = [H_LEVEL] & [DATA_SOURCE] lineageTag: d70dc1a7-f744-43f0-ae43-6f8acf0aad9f summarizeBy: none annotation SummarizationSetBy = Automatic column REIMBURSETYPE_SOURCE = [REIMBURSE_TYPE] & [DATA_SOURCE] lineageTag: beb9f5b1-422d-4432-9d52-5083dad9f0db summarizeBy: none annotation SummarizationSetBy = Automatic column REIMBURSE_SOURCE = [REIMBURSE] & [DATA_SOURCE] lineageTag: 02bac298-0bb2-44d6-9c74-9bf37f75af03 summarizeBy: none annotation SummarizationSetBy = Automatic column PRESCRIPTION_TYPE dataType: string lineageTag: 45ea0f30-12b1-4e20-93ea-e5cb2d06828a summarizeBy: none sourceColumn: PRESCRIPTION_TYPE annotation SummarizationSetBy = Automatic column MOLECULE_SOURCE = RELATED(Dim_PackInfo[MOLECULE_SOURCE]) lineageTag: a321cab7-5b9d-475b-b7c3-ceb13dd25358 summarizeBy: none annotation SummarizationSetBy = Automatic column Temp_RemovBF202101 = INT( OR( [DATA_SOURCE] = "IQVIA-CHPA(Monthly)" && [DATE_KEY] >=DATE(2021,1,1), [DATA_SOURCE] <> "IQVIA-CHPA(Monthly)" )) formatString: 0 lineageTag: 324acddb-9483-4766-af2f-d0bb8fe45c5c summarizeBy: sum annotation SummarizationSetBy = Automatic column Is_SpecialFlag = SWITCH(TRUE(), LEFT([PACK_COD],4) = "AZP_",1,0) formatString: 0 lineageTag: cb145333-c744-4a16-9fe9-83d1f15095df summarizeBy: sum annotation SummarizationSetBy = Automatic column AIA_REMOVE dataType: int64 formatString: 0 lineageTag: 030fa2f5-e4e8-4131-9ab6-ae10adfc23e0 summarizeBy: sum sourceColumn: AIA_REMOVE annotation SummarizationSetBy = Automatic column MERGEDATA_FLAG dataType: string lineageTag: bd306596-c091-4a77-a860-c832a98c3144 summarizeBy: none sourceColumn: MERGEDATA_FLAG annotation SummarizationSetBy = Automatic column MERGEDATA_FLAG_MKT dataType: string lineageTag: 969b33d4-3692-4790-8ddb-b063273f21d5 summarizeBy: none sourceColumn: MERGEDATA_FLAG_MKT annotation SummarizationSetBy = Automatic column MERGEDATA_FLAG_CHAP_DTP_NOAIA = SWITCH(TRUE() ,[DATA_SOURCE] = "iqvia-chpa(monthly)" ,"CHPA",[DTP_FLAG] = 1,"DTP","Normal") lineageTag: 5ddd1cab-ea1e-4ebd-93cb-158b66492f9c summarizeBy: none annotation SummarizationSetBy = Automatic column MERGEDATA_FLAG_CHPA_DTP dataType: int64 formatString: 0 lineageTag: dae76934-1c24-4f47-8c5b-fd70e2ea16c4 summarizeBy: sum sourceColumn: MERGEDATA_FLAG_CHPA_DTP annotation SummarizationSetBy = Automatic column MERGEDATA_FLAG_DTP_NOROC dataType: int64 formatString: 0 lineageTag: fe8a83cb-c9b9-4e9d-b424-a483d6abf25b summarizeBy: sum sourceColumn: MERGEDATA_FLAG_DTP_NOROC annotation SummarizationSetBy = Automatic column MERGED_DATA_DTP_OTHERS dataType: string lineageTag: 16de7a31-4835-44bc-bc88-038e8f7247a9 summarizeBy: none sourceColumn: MERGED_DATA_DTP_OTHERS changedProperty = DataType annotation SummarizationSetBy = Automatic column EGFR_FLAG dataType: int64 formatString: 0 lineageTag: 43b1127c-5c48-406b-ab40-0c472b7b1677 summarizeBy: sum sourceColumn: EGFR_FLAG annotation SummarizationSetBy = Automatic partition Fact_Sales = m mode: import queryGroup: 非首页 source = ``` let Source = Value.NativeQuery( Databricks.Catalogs( ServerAddress, HttpPath, [ Catalog = CatalogName, Database = null, EnableAutomaticProxyDiscovery = null,EnableQueryResultDownload="0" ] ){ [Name = CatalogName, Kind = "Database"] }[Data], " with mergedata( select pack_cod AS PC ,AUDIT_COD AS AC ,DATA_SOURCE as DS ,SOURCENAME ,CONFIG_DATASOURCE from dws.dws_external_merge_data_config ) ,all_egfr_pack as ( select PACK_COD,PROD_DES_C from DM.dm_td_external_market_pack_mapping where DATA_SOURCE = 'Retail(Quarterly)' and MARKET = 'EGFR TKI Market' group by 1,2 ) ,4brand as ( select PACK_COD from all_egfr_pack where PROD_DES_C in ('阿美乐','泰瑞沙','凯美纳','艾弗沙') group by 1 ) ,otherbrand as ( select PACK_COD from all_egfr_pack where PACK_COD not in ( select PACK_COD from `4brand` group by 1) group by 1 ) ,all_external_sales( select t0.*,if(t1.pack_cod is null ,1,2) MERGED_DATA_DTP_OTHERS from DM.DM_TF_EXTERNAL_SALES t0 left join otherbrand t1 on t1.pack_cod = t0.PACK_COD and t0.DATA_SOURCE = 'Retail(Quarterly)' union all select *,3 AS MERGED_DATA_DTP_OTHERS FROM dm.dm_tf_exteranl_sales_merged_data_dtp_others ) SELECT YYYYMM,A.PACK_COD,CORP_COD,A.AUDIT_COD,PLATFORM_TYPE,STORE_NAME,STORE_TYPE,REGION_TYPE,A.DATA_SOURCE,PACK_FLAG ,CASE WHEN C.PACK_CODE IS NOT NULL THEN 1 ELSE A.PROD_FLAG END AS PROD_FLAG ,DTP_FLAG,INT(CMPS_FLAG) CMPS_FLAG,NEW_CODE, A.INST_CODE, B.AIA_HP_FLAG, DEPT_NAME,ROUND(H_LEVEL,0) H_LEVEL,REIMBURSE,REIMBURSE_TYPE,PRESCRIPTION_SOURCE PRESCRIPTION_TYPE, CASE WHEN SALES_UNIT_CAL = 0 THEN NULL ELSE CAST(SALES_UNIT_CAL AS DECIMAL(35,10)) END SALES_UNIT_CAL, CASE WHEN SALES_UNIT_CAL_LY = 0 THEN NULL when A.DATA_SOURCE='IQVIA-CHPA(Monthly)' AND A.YYYYMM<=202010 then NULL ELSE CAST(SALES_UNIT_CAL_LY AS DECIMAL(35,10)) END SALES_UNIT_CAL_LY, CASE WHEN SALES_VALUE_CAL = 0 THEN NULL ELSE CAST(SALES_VALUE_CAL AS DECIMAL(35,10)) END SALES_VALUE_CAL, CASE WHEN SALES_VALUE_CAL_LY = 0 THEN NULL when A.DATA_SOURCE='IQVIA-CHPA(Monthly)' AND A.YYYYMM<=202010 then NULL ELSE CAST(SALES_VALUE_CAL_LY AS DECIMAL(35,10)) END SALES_VALUE_CAL_LY, CASE WHEN CONUTING_UNIT = 0 THEN NULL ELSE CAST(CONUTING_UNIT AS DECIMAL(35,10)) END CONUTING_UNIT, CASE WHEN CONUTING_UNIT_LY = 0 THEN NULL when A.DATA_SOURCE='IQVIA-CHPA(Monthly)' AND A.YYYYMM<=202010 then NULL ELSE CAST(CONUTING_UNIT_LY AS DECIMAL(35,10)) END CONUTING_UNIT_LY, CASE WHEN prescription = 0 THEN NULL ELSE CAST(prescription AS DECIMAL(35,10)) END PRESCRIPTION, CASE WHEN prescription_ly = 0 THEN NULL when A.DATA_SOURCE='IQVIA-CHPA(Monthly)' AND A.YYYYMM<=202010 then NULL ELSE CAST(prescription_ly AS DECIMAL(35,10)) END PRESCRIPTION_LY --AIA remove ins which is wrong ,1 AS AIA_REMOVE --Merged Data part ,CASE WHEN A.DTP_FLAG = '1' THEN 'DTP' WHEN E.SOURCENAME IS NULL THEN 'Normal' ELSE E.SOURCENAME END AS MERGEDATA_FLAG --mark ins and pack by brandtable ,CASE WHEN A.DTP_FLAG = '1' THEN 'DTP' WHEN F.SOURCENAME IS NULL THEN 'Normal' ELSE F.SOURCENAME END AS MERGEDATA_FLAG_MKT --mark ins and pack by markettable ,CASE WHEN A.DTP_FLAG = '1' THEN 1 WHEN A.DATA_SOURCE='IQVIA-CHPA(Monthly)' THEN 1 ELSE 0 END AS MERGEDATA_FLAG_CHPA_DTP --mark chap and dtp data ,CASE WHEN A.DTP_FLAG = '1' AND A.AUDIT_COD ='ROC' THEN 0 ELSE 1 END AS MERGEDATA_FLAG_DTP_NOROC --mark dtp roc with 0 ,MERGED_DATA_DTP_OTHERS ,CASE WHEN G.AUDIT_COD IS NULL THEN 0 ELSE 1 END AS EGFR_FLAG FROM all_external_sales A LEFT JOIN dm.dm_aia_hp_flag B ON A.inst_code = B.inst_code AND A.DATA_SOURCE = B.DATA_SOURCE LEFT JOIN ( select PACK_COD AS PACK_CODE from dm.dm_td_external_market_pack_mapping where MARKET in ('NIAD (Non-Insulin Anti-Diabetic) Market','Inhaled Extended Market','BUDESONIDE Market') and DATA_SOURCE = 'Retail(Quarterly)' group by 1 ) C ON A.PACK_COD = C.PACK_CODE AND A.DATA_SOURCE = 'Retail(Quarterly)' --LEFT JOIN dm.dm_td_aia_remove_special_ins D ON A.AUDIT_COD = D.ins_code AND A.PACK_COD = D.PACK_COD AND A.DATA_SOURCE = 'AIA(Monthly)' -- add flag which should be merged LEFT JOIN mergedata E ON A.DATA_SOURCE = E.DS AND A.AUDIT_COD = E.AC AND A.PACK_COD = E.PC AND E.CONFIG_DATASOURCE = 'pack' LEFT JOIN mergedata F ON A.DATA_SOURCE = F.DS AND A.AUDIT_COD = F.AC AND A.PACK_COD = F.PC AND F.CONFIG_DATASOURCE = 'market' --hotfix egfr market 天津 LEFT JOIN dws.dws_external_merge_data_config_special G ON A.AUDIT_COD = G.AUDIT_COD AND A.PACK_COD = G.PACK_COD AND A.DATA_SOURCE = G.DATA_SOURCE -- hotfix GR YM<=201910 数据存在问题 where (A.DATA_SOURCE='IQVIA-CHPA(Monthly)' AND A.YYYYMM>201910) OR A.DATA_SOURCE<>'IQVIA-CHPA(Monthly)' " & UsingLimit, null, [ EnableFolding = true ] ) in Source ``` annotation PBI_ResultType = Exception annotation PBI_NavigationStepName = Navigation