table Dim_HP_Potential_AIA lineageTag: 7cdaf160-044f-47b1-ab65-9b02711bc251 column INST_CODE dataType: string lineageTag: 1593b925-1c0e-41b7-b823-0a5fa0ea8581 summarizeBy: none sourceColumn: INST_CODE annotation SummarizationSetBy = Automatic column DATA_SOURCE dataType: string lineageTag: bce6806b-92d5-4fea-a487-1555a2c4a2b6 summarizeBy: none sourceColumn: DATA_SOURCE annotation SummarizationSetBy = Automatic column AUDIT_COD dataType: string lineageTag: 1eedd830-ebc1-4288-906c-f16cb73010f4 summarizeBy: none sourceColumn: AUDIT_COD annotation SummarizationSetBy = Automatic column INST_SOURCE = ``` [INST_CODE] & [DATA_SOURCE] ``` lineageTag: 99832462-da4e-4cef-9ffd-bf5bf56897db summarizeBy: none annotation SummarizationSetBy = Automatic column INS_MKT_SOURCE = IF([DATA_SOURCE] = "AIA(Monthly)", [AUDIT_COD] & [MARKET] & [DATA_SOURCE], [DATA_SOURCE]) lineageTag: ef9b9249-e3ec-4023-b3a1-540a604aa356 summarizeBy: none annotation SummarizationSetBy = Automatic column IS_DEFAULT_MKT_FLAG = VAR flag = IF (Dim_HP_Potential_AIA[IS_DEFAULT_POTENTIAL_MKT] = "1" && Dim_HP_Potential_AIA[IS_DEFAULT_POTENTIAL_TYPE] = "1", "Default", "") RETURN flag lineageTag: 5c311ed7-92c9-4fab-bd89-0d0d68cddaba summarizeBy: none annotation SummarizationSetBy = Automatic column IS_DEFAULT_TYPE_FLAG = IF([IS_DEFAULT_POTENTIAL_TYPE] = "1" , "Default") lineageTag: 15ed6338-cf5d-4899-bf68-d1d00c17de9e summarizeBy: none annotation SummarizationSetBy = Automatic column MKT_SOURCE = ``` [MARKET] & [DATA_SOURCE] ``` lineageTag: 2771c8b9-ecbe-454d-b220-1245c50933d6 summarizeBy: none annotation SummarizationSetBy = Automatic column MARKET dataType: string lineageTag: c0acf484-8b6b-4383-8c92-cf788ad7643e summarizeBy: none sourceColumn: MARKET annotation SummarizationSetBy = Automatic column POTENTIAL_MKT_CODE dataType: string lineageTag: 6e07d663-a549-464f-89c1-8153907caba2 summarizeBy: none sourceColumn: POTENTIAL_MKT_CODE annotation SummarizationSetBy = Automatic column POTENTIAL_MKT_NAME dataType: string lineageTag: 20933df6-b80a-4b4d-8af1-5c20d198b72d summarizeBy: none sourceColumn: POTENTIAL_MKT_NAME annotation SummarizationSetBy = Automatic column IS_DEFAULT_POTENTIAL_MKT dataType: string lineageTag: c73ec8ad-3dc6-4511-9c2a-ffb3bed03f8b summarizeBy: none sourceColumn: IS_DEFAULT_POTENTIAL_MKT annotation SummarizationSetBy = Automatic column POTENTIAL_TYPE_CODE dataType: string lineageTag: 45953c49-c8c2-4483-8b2b-cb48f22cfc65 summarizeBy: none sourceColumn: POTENTIAL_TYPE_CODE annotation SummarizationSetBy = Automatic column POTENTIAL_TYPE_NAME dataType: string lineageTag: 3c8fca54-985f-4dc0-b5c9-603d029aa44c summarizeBy: none sourceColumn: POTENTIAL_TYPE_NAME annotation SummarizationSetBy = Automatic column IS_DEFAULT_POTENTIAL_TYPE dataType: string lineageTag: 6a95c82d-3d20-41fc-bd9c-b588503ceb43 summarizeBy: none sourceColumn: IS_DEFAULT_POTENTIAL_TYPE annotation SummarizationSetBy = Automatic column POTENTIAL_VALUE dataType: double lineageTag: 8e97ef6b-59bf-4931-a5f5-dc154b119047 summarizeBy: sum sourceColumn: POTENTIAL_VALUE annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isGeneralNumber":true} column DECILE_GROUP dataType: string lineageTag: 11a21420-5976-4851-88c4-17f69a9fc827 summarizeBy: none sourceColumn: DECILE_GROUP annotation SummarizationSetBy = Automatic column DECILE_ORDER dataType: int64 formatString: 0 lineageTag: 45b3ba27-4159-44e8-a3b5-c6096e351c76 summarizeBy: sum sourceColumn: DECILE_ORDER annotation SummarizationSetBy = Automatic column DECILE dataType: string lineageTag: 50c26295-0304-4635-962f-8af21b423278 summarizeBy: none sourceColumn: DECILE sortByColumn: DECILE_ORDER changedProperty = SortByColumn annotation SummarizationSetBy = Automatic column TARGET_INS_LEVEL dataType: string lineageTag: a8ae1f31-beb5-410d-86cd-0680f288e9cc summarizeBy: none sourceColumn: TARGET_INS_LEVEL annotation SummarizationSetBy = Automatic partition Dim_HP_Potential_AIA = m mode: import queryGroup: 'AIA 独有表' source = ``` let Source = Value.NativeQuery( Databricks.Catalogs( ServerAddress, HttpPath, [ Catalog = CatalogName, Database = null, EnableAutomaticProxyDiscovery = null,EnableQueryResultDownload="0" ] ){ [Name = CatalogName, Kind = "Database"] }[Data], " with external_sales as ( select distinct t1.INST_CODE, t2.market, t1.DATA_SOURCE, t1.AUDIT_COD from dm.dm_tf_external_sales t1 left join dm.dm_td_external_market_pack_mapping t2 on t1.DATA_SOURCE = t2.DATA_SOURCE and t1.PACK_COD = t2.PACK_COD ) select distinct t1.INST_CODE, t1.MARKET, t2.POTENTIAL_MKT_CODE, t2.POTENTIAL_MKT_NAME, t2.IS_DEFULT_POTENTIAL_MKT IS_DEFAULT_POTENTIAL_MKT, t2.POTENTIAL_TYPE_CODE, t2.POTENTIAL_TYPE_NAME, t2.IS_DEFULT_POTENTIAL_TYPE IS_DEFAULT_POTENTIAL_TYPE, t2.POTENTIAL_VALUE, t2.DECILE_GROUP, NVL(t2.DECILE_ORDER,99) as DECILE_ORDER, t2.DECILE, t1.DATA_SOURCE, t1.AUDIT_COD, NVL(t3.TARGET_INS_LEVEL,'') as TARGET_INS_LEVEL from external_sales t1 left join dm.dm_td_aia_inst_mkt t2 on t1.AUDIT_COD = t2.audit_cod and t1.market = t2.market and t1.DATA_SOURCE = t2.DATA_SOURCE left join dm.dm_aia_targethp_flag t3 on t1.AUDIT_COD = t3.inst_code and t1.market = t3.MARKET and t1.DATA_SOURCE = t3.DATA_SOURCE --SELECT DISTINCT A.INST_CODE, B.MARKET, POTENTIAL_MKT_CODE, POTENTIAL_MKT_NAME, IS_DEFULT_POTENTIAL_MKT, -- POTENTIAL_TYPE_CODE, POTENTIAL_TYPE_NAME, IS_DEFULT_POTENTIAL_TYPE, POTENTIAL_VALUE, -- DECILE_GROUP, NVL(DECILE_ORDER, 99) DECILE_ORDER, DECILE, A.DATA_SOURCE, B.AUDIT_COD, NVL(C.TARGET_INS_LEVEL, '') TARGET_INS_LEVEL, D.AIA_HP_FLAG -- FROM DM.DM_TF_EXTERNAL_SALES A -- LEFT JOIN dm.dm_td_aia_inst_mkt B ON A.inst_code = B.inst_code AND A.DATA_SOURCE = B.DATA_SOURCE -- LEFT JOIN dm.dm_aia_targethp_flag C ON C.inst_code = B.inst_code AND C.DATA_SOURCE = B.DATA_SOURCE AND C.MARKET = B.MARKET -- LEFT JOIN dm.dm_aia_hp_flag D ON A.inst_code = D.inst_code AND A.DATA_SOURCE = D.DATA_SOURCE " & UsingLimit, null, [ EnableFolding = true ] ) in Source ``` annotation PBI_ResultType = Table annotation PBI_NavigationStepName = 导航