table Dim_Org lineageTag: 85f1d649-64ef-43b0-85a7-8dd1e01437f3 column REGION_CODE_DESC = [REGION_CODE] & " - " & [REGION_NAME_EN] lineageTag: 1654abce-f007-4294-91ad-f420a5b23805 summarizeBy: none annotation SummarizationSetBy = Automatic column RC_CODE_DESC = [RC_CODE] & " - " & [RC_NAME_EN] lineageTag: 40f38a30-1a2a-4867-bca9-42ff6c147ffe summarizeBy: none annotation SummarizationSetBy = Automatic column region_name_en dataType: string lineageTag: d5f68ced-e54c-436f-87ae-6bf9c757cd73 summarizeBy: none sourceColumn: region_name_en changedProperty = SortByColumn annotation SummarizationSetBy = Automatic column rc_code dataType: string lineageTag: ed896ef1-79fc-48f9-b2bd-fac125d243e9 summarizeBy: none sourceColumn: rc_code annotation SummarizationSetBy = Automatic column rc_name_en dataType: string lineageTag: 4447c25d-e0c5-4395-932c-3f4875b8050e summarizeBy: none sourceColumn: rc_name_en changedProperty = SortByColumn annotation SummarizationSetBy = Automatic column bu_category dataType: string lineageTag: 34888f0a-251d-4489-844a-34b759458c3a summarizeBy: none sourceColumn: bu_category annotation SummarizationSetBy = Automatic column bu_name_en dataType: string lineageTag: 35d50f36-950c-4daa-b239-706081a47eb6 summarizeBy: none sourceColumn: bu_name_en annotation SummarizationSetBy = Automatic column sub_bu_name dataType: string lineageTag: 06254d64-a12d-4e4b-8e58-15309bd030b1 summarizeBy: none sourceColumn: sub_bu_name annotation SummarizationSetBy = Automatic column team dataType: string lineageTag: bea0a9cb-bff8-4759-8349-2591288eb7ac summarizeBy: none sourceColumn: team annotation SummarizationSetBy = Automatic column dsm_kcode dataType: string lineageTag: acf34109-0acd-4d86-a7d8-2c00c95773da summarizeBy: none sourceColumn: dsm_kcode annotation SummarizationSetBy = Automatic column dsm_name dataType: string lineageTag: f4307294-8461-4ed0-ad0f-f4b3069d193e summarizeBy: none sourceColumn: dsm_name annotation SummarizationSetBy = Automatic column mr_trty_code dataType: string lineageTag: d0dd1430-fe90-40f0-82d4-79f56477c26e summarizeBy: none sourceColumn: mr_trty_code annotation SummarizationSetBy = Automatic column mr_kcode dataType: string lineageTag: d9e44f07-8f15-408d-8972-dc9e8d84a9bd summarizeBy: none sourceColumn: mr_kcode annotation SummarizationSetBy = Automatic column mr_name dataType: string lineageTag: 29393d71-6eba-48f4-8909-f07e100b2e29 summarizeBy: none sourceColumn: mr_name annotation SummarizationSetBy = Automatic column RC_NAME dataType: string lineageTag: 840b5add-6f02-4cf4-8032-664db94fed75 summarizeBy: none sourceColumn: RC_NAME annotation SummarizationSetBy = Automatic column region_code dataType: string lineageTag: 19adebc6-97b2-4e00-acba-782fa49baa2d summarizeBy: none sourceColumn: region_code annotation SummarizationSetBy = Automatic column nsd_kcode dataType: string lineageTag: dbbbac41-38d3-497e-9697-1e01d48d08bd summarizeBy: none sourceColumn: nsd_kcode annotation SummarizationSetBy = Automatic column nsd_name dataType: string lineageTag: f1bcfe22-4e68-4ab3-a044-cd784649a1ed summarizeBy: none sourceColumn: nsd_name annotation SummarizationSetBy = Automatic column rsd_kcode dataType: string lineageTag: 8d9aa51e-3885-4b45-8ad8-a53a2da65231 summarizeBy: none sourceColumn: rsd_kcode annotation SummarizationSetBy = Automatic column rsd_name dataType: string lineageTag: b8baede2-1403-47f0-98aa-177fb349a9a9 summarizeBy: none sourceColumn: rsd_name annotation SummarizationSetBy = Automatic column rsm_kcode dataType: string lineageTag: ffc0d175-563b-4b9f-88b3-31fab7d1c459 summarizeBy: none sourceColumn: rsm_kcode annotation SummarizationSetBy = Automatic column rsm_name dataType: string lineageTag: fd0d91aa-7f4b-4f51-a70a-4436df0428a7 summarizeBy: none sourceColumn: rsm_name annotation SummarizationSetBy = Automatic column rc_name_en_n dataType: string lineageTag: 7451241f-6d33-4a10-b3ab-4d0f5de35c4c summarizeBy: none sourceColumn: rc_name_en_n annotation SummarizationSetBy = Automatic column region_name_en_n dataType: string lineageTag: 37737e9c-e383-4124-b8ff-a91a7ea88df2 summarizeBy: none sourceColumn: region_name_en_n annotation SummarizationSetBy = Automatic partition Dim_Org-00dafd9e-60ac-4454-a463-b2187d119357 = 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], // " // SELECT DISTINCT A.REGION_CODE,A.region_name_en,A.rc_code,A.rc_name_en,A.RegionCenter,A.AUDIT_COD,A.MARKET, // CASE WHEN bu_category IN ('All Channel','EC') AND A.DATA_SOURCE = 'IQVIA-CHPA(Monthly)' THEN 'ROC' WHEN A.region_name_en = 'ROC' THEN 'ROC' WHEN A.rc_name_en = 'ROC' THEN 'ROC' ELSE bu_category END bu_category, // CASE WHEN bu_category IN ('All Channel','EC') AND A.DATA_SOURCE = 'IQVIA-CHPA(Monthly)' THEN 'ROC' WHEN A.region_name_en = 'ROC' THEN 'ROC' WHEN A.rc_name_en = 'ROC' THEN 'ROC' ELSE bu_name_en END bu_name_en, // CASE WHEN bu_category IN ('All Channel','EC') AND A.DATA_SOURCE = 'IQVIA-CHPA(Monthly)' THEN 'ROC' WHEN A.region_name_en = 'ROC' THEN 'ROC' WHEN A.rc_name_en = 'ROC' THEN 'ROC' ELSE sub_bu_name END sub_bu_name,sub_bu_code_auth,bu_code_auth, // A.DATA_SOURCE,REGION_RATIO,CASE WHEN bu_category IN ('All Channel','EC') AND A.DATA_SOURCE = 'IQVIA-CHPA(Monthly)' THEN 'ROC' WHEN A.region_name_en = 'ROC' THEN 'ROC' ELSE RSM_Name END RSM_Name, // CASE WHEN bu_category IN ('All Channel','EC') AND A.DATA_SOURCE = 'IQVIA-CHPA(Monthly)' THEN 'ROC' WHEN A.rc_name_en = 'ROC' THEN 'ROC' ELSE A.RC_Name END RC_Name, // CASE WHEN A.region_name_en = 'ROC' THEN 2 ELSE 1 END REGION_RN,CASE WHEN A.rc_name_en = 'ROC' THEN 2 ELSE 1 END RC_RN, // team, // dsm_kcode, // dsm_name, // mr_trty_code, // mr_kcode, // mr_name, // -- B.INST_CODE,NVL(B.inst_name,'') INST_NAME,B.INST_TYPE, B.INST_SUB_TYPE, B.org_level_desc INS_LEVEL, // C.IS_TARGET_HP, C.SUB_CHANNEL , C.DRAGON_FLAG, C.KA_FLAG // ,C.SUB_CHANNEL_ORDER // FROM dm.dm_td_external_org A // -- left join dm.dm_td_institution B on A.AUDIT_COD = NVL(B.INST_CODE, 'ROC') // LEFT JOIN dm.dm_aia_targethp_flag C ON A.AUDIT_COD = NVL(C.INST_CODE, 'ROC') AND A.DATA_SOURCE = C.DATA_SOURCE AND A.MARKET = C.MARKET // --WHERE (DATA_SOURCE = 'IQVIA-CHPA(Monthly)' AND bu_category NOT IN ('All Channel','EC')) OR DATA_SOURCE <> 'IQVIA-CHPA(Monthly)' "WITH dm_td_org_temp AS ( SELECT org_key, mr_trty_tag, district_code, rsm_name, rc_name, p2.region_name, p2.rsd_kcode, p2.rsd_name, CASE WHEN p2.yyyymm >= '202601' AND p2.bu_code = 'PC12' THEN 'PC09' ELSE p2.bu_code END bu_code, CASE WHEN p2.yyyymm >= '202601' AND p2.bu_report = 'EC' THEN 'Retail' ELSE p2.bu_report END bu_report, CASE WHEN p2.yyyymm >= '202601' AND p2.sub_bu_code = 'NA27' THEN 'NA06' ELSE p2.sub_bu_code END sub_bu_code, CASE WHEN p2.yyyymm >= '202601' AND p2.sub_bu_name = 'EC' THEN 'Retail' ELSE p2.sub_bu_name END sub_bu_name, CASE WHEN p2.yyyymm >= '202601' AND p2.sub_bu_name_en = 'EC' THEN 'Retail' ELSE p2.sub_bu_name_en END sub_bu_name_en, CASE WHEN p2.yyyymm >= '202601' AND p2.bu_name = 'EC' THEN 'Retail' ELSE p2.bu_name END bu_name, CASE WHEN p2.yyyymm >= '202601' AND p2.bu_name_en = 'EC' THEN 'Retail' ELSE p2.bu_name_en END bu_name_en, CASE WHEN p2.yyyymm >= '202601' AND p2.nsd_kcode = 'ND_EC0001_V' THEN 'KSGX559' ELSE p2.nsd_kcode END nsd_kcode, CASE WHEN p2.yyyymm >= '202601' AND p2.bu_name = 'EC' AND p2.nsd_name = 'Vacant' THEN '李白杨' ELSE p2.nsd_name END nsd_name, CASE WHEN p2.yyyymm >= '202601' AND p2.nsd_trty_code = 'ND_EC0001' THEN 'ND_RT0001' ELSE p2.nsd_trty_code END nsd_trty_code, CASE WHEN p2.yyyymm >= '202601' AND p2.bsd_kcode = 'VP_EC0001_V' THEN 'VP_RT0001_V' ELSE p2.bsd_kcode END bsd_kcode, CASE WHEN p2.yyyymm >= '202601' AND p2.bsd_trty_code = 'VP_EC0001' THEN 'VP_RT0001' ELSE p2.bsd_trty_code END bsd_trty_code, p2.rsm_kcode, p2.rc_code, p2.rc_name_en, p2.region_code, p2.region_name_en, BU_CATEGORY, yyyymm, mr_trty_code, mr_kcode, dsm_trty_code, rsm_trty_code, dsm_kcode, rsd_trty_code, district_name, dsm_name, mr_name FROM DM.dm_td_org P2 WHERE yyyymm IN ( SELECT key_value FROM dm.dm_td_date_config WHERE key_code = 'pfmc_cvh' ) AND mr_trty_code <> '' ) SELECT DISTINCT mr_trty_code, bu_category, bu_name_en, sub_bu_name, rc_code, rc_name AS rc_name_en, rc_name_en AS rc_name_en_n, CASE WHEN rsd_name = 'Vacant' THEN rsd_kcode ELSE rsd_name END AS RC_NAME, region_code, region_name AS region_name_en, region_name_en AS region_name_en_n, district_name AS team, nsd_kcode, CASE WHEN nsd_name = 'Vacant' THEN nsd_kcode ELSE nsd_name END AS nsd_name, rsd_kcode, CASE WHEN rsd_name = 'Vacant' THEN rsd_kcode ELSE rsd_name END AS rsd_name, rsm_kcode, CASE WHEN rsm_name = 'Vacant' THEN rsm_kcode ELSE rsm_name END AS rsm_name, dsm_kcode, CASE WHEN dsm_name = 'Vacant' THEN dsm_kcode ELSE dsm_name END AS dsm_name, mr_kcode, CASE WHEN mr_name = 'Vacant' THEN mr_kcode ELSE mr_name END AS mr_name FROM dm_td_org_temp UNION SELECT DISTINCT rsm_trty_code AS mr_trty_code, bu_category, bu_name_en, sub_bu_name, rc_code, rc_name AS rc_name_en, rc_name_en AS rc_name_en_n, CASE WHEN rsd_name = 'Vacant' THEN rsd_kcode ELSE rsd_name END AS RC_NAME, region_code, region_name AS region_name_en, region_name_en AS region_name_en_n, '' AS team, nsd_kcode, CASE WHEN nsd_name = 'Vacant' THEN nsd_kcode ELSE nsd_name END AS nsd_name, rsd_kcode, CASE WHEN rsd_name = 'Vacant' THEN rsd_kcode ELSE rsd_name END AS rsd_name, rsm_kcode, CASE WHEN rsm_name = 'Vacant' THEN rsm_kcode ELSE rsm_name END AS rsm_name, '' AS dsm_kcode, '' AS dsm_name, '' AS mr_kcode, '' AS mr_name FROM dm_td_org_temp UNION ALL SELECT 'Vacant' AS mr_trty_code, 'ROC' AS bu_category, 'ROC' AS bu_name_en, 'ROC' AS sub_bu_name, 'ROC' AS rc_code, 'ROC' AS rc_name_en, 'ROC' AS rc_name_en_n, 'ROC' AS RC_NAME, 'ROC' AS region_code, 'ROC' AS region_name_en, 'ROC' AS region_name_en_n, 'ROC' AS team, 'Vacant' AS nsd_kcode, 'Vacant' AS nsd_name, 'Vacant' AS rsd_kcode, 'Vacant' AS rsd_name, 'Vacant' AS rsm_kcode, 'Vacant' AS rsm_name, 'Vacant' AS dsm_kcode, 'Vacant' AS dsm_name, 'Vacant' AS mr_kcode, 'Vacant' AS mr_name UNION ALL SELECT DISTINCT concat ('ROC_', nsd_trty_code) AS mr_trty_code, 'ROC' AS bu_category, 'ROC' AS bu_name_en, 'ROC' AS sub_bu_name, 'ROC' AS rc_code, 'ROC' AS rc_name_en, 'ROC' AS rc_name_en_n, 'ROC' AS RC_NAME, 'ROC' AS region_code, 'ROC' AS region_name_en, 'ROC' AS region_name_en_n, 'ROC' AS team, nsd_kcode, nsd_name, 'Vacant' AS rsd_kcode, 'Vacant' AS rsd_name, 'Vacant' AS rsm_kcode, 'Vacant' AS rsm_name, 'Vacant' AS dsm_kcode, 'Vacant' AS dsm_name, 'Vacant' AS mr_kcode, 'Vacant' AS mr_name FROM dm_td_org_temp " & UsingLimit, null, [ EnableFolding = true ] ) in Source ``` annotation PBI_ResultType = Table annotation PBI_NavigationStepName = 导航