It's quite a lot of fields, so I thought it would be too long to paste.
Thanks for the detail that does make sense, but all those fields are new (Except for Policy Key). It should be as simple as attaching extra fields on the end based on policy key. I have heard others mention that mapping load is preferential to join, but I still don't see why this would lag out.
ProfitDetails:
LOAD
RowNo()+Peek([Transaction ID],-1,'AirDetails') as [Transaction ID],
_Policy_Key as [Policy Key],
ACCT_YR as [Account Year],
ACCT_MO as [Account Month],
ACTG_YR_MO as [Account YearMonth],
Autonumber(_Policy_Key&'-'&ACTG_YR_MO,'PolYrMo') as [Policy YrMo Key],
Date(Date#( [ACTG_YR_MO] , 'YYYYMM')) as [Trans Reg Date],
SRCE_POL_N as [Policy Number],
SRCE_POL_EFF_D as [Policy Eff Date],
SRCE_POL_EXP_D as [Policy Exp Date],
INSD_NA as [Insured Name],
UNWTR_NA as [Underwriter Name],
NEW_RNWL_C as [New Renewal Code],
NEW_RNWL_DE as [New Renewal Desc],
SRCE_STUY_CO_C as [Writing Company Code],
SRCE_STUY_CO_NA as [Writing Company],
CMGMT_DIVS_C as [Division Code],
CMGMT_DIVS_DE as [Division Desc],
CMGMT_MKTG_RESP_C as [Marketing Responsibility Code],
CMGMT_MKTG_RESP_DE as [Marketing Responsibility Desc],
CMGMT_MGMT_RESP_C as [Management Responsibility Code],
CMGMT_MGMT_RESP_DE as [Management Responsibility Desc],
CMGMT_PRFY_ANL_C as [Profitabiltiy Analysis Code],
CMGMT_PRFY_ANL_DE as [Profitabiltiy Analysis Desc],
MCC_C as [MCC Code],
MCC_DE as [MCC Desc],
SRCE_GFS_PRDT_C as [Product Code],
SRCE_GFS_PRDT_DE as [Product Desc],
Text(IF(ISNULL(PROD_ID),CUR_PROD_ID,PROD_ID)) as [Producer ID],
IF(NOT ISNULL(PROD_ID) and CUR_PROD_ID = SRCE_SUB_PROD_C, SRCE_SRCE_PROD_NA, Null()) as [Producer Name],
INS_C as [Insurance Type Code],
Capitalize(INS_CD_DE) as [Insurance Type Desc],
Text(IF(ISNULL(RE_TRTY_C), RE_CO_C, RE_TRTY_C)) as [Reinsurance Company/Treaty Code],
IF(ISNULL(SRCE_RE_CO_DE), SRCE_RE_ASMD_CO_DE, SRCE_RE_CO_DE) as [Reinsurance Company Name],
Reinsur_Ident as [Reinsurance Contract Type],
SRCE_ENT_C as [Transaction Code],
SRCE_ENT_CD_DE as [Transaction Desc],
Autonumber(_Policy_Key&'-'&ACTG_YR_MO&'-'&SRCE_ENT_C,'PolYrMoTran') as [Policy YrMo Tran Key], //Mostly for Extract 29
Date(MakeDate(ATTCHNG_CTRY_Y,ATTCHNG_MO,ATTCHNG_DA),'YYYYMMDD') as [Transaction Eff Date],
//Match signs to Air Data for Extract Consistency
If(INS_C='CED',-USD_WP_MTH_A,USD_WP_MTH_A) as [Written Premium],
If(INS_C='DIR' or INS_C='ASM',-USD_COMM_MTH_A,USD_COMM_MTH_A) as Commission,
If(TAX_PLTDV_C='',Null(),TAX_PLTDV_C) as [State Code],
Capitalize(TAX_PLTDV_DE) as State,
BUS_CLSN_C as [Business Class Code],
POL_EFF_YR as [Policy Effective Year],
SRCE_SYS_SRCE_C as [Source System Code],
SRCE_SYS_SRCE_DE as [Source System],
SRCE_GFS_BU_C as [Business Unit Code],
SRCE_GFS_BU_DE as [Business Unit],
TAX_CTRY_ID as [Country ID],
TAX_CTRY_DE as [Country Desc],
INSD_N as [PROFIT Insured Number],
CUR_PROD_ID as [PROFIT Current Producer ID],
SRCE_SRCE_PROD_C as [PROFIT Lchubb Producer ID],
SRCE_SUB_PROD_C as [PROFIT Sub Producer Code],
SRCE_SRCE_PROD_NA as [PROFIT Lchubb Producer Name],
SRCE_RE_ASMD_CO_C as [PROFIT Assumed Reinsurance Comp Code],
SRCE_RE_CO_C as [PROFIT Reinsurance Comp Code],
SRCE_RE_CVR_DE as [PROFIT Reinsurance Coverage Desc],
LAC_WP_MTH_A as [PROFIT Local Written Premium],
LAC_COMM_MTH_A as [PROFIT Local Commission],
SRCE_GFS_DEPT_C as [PROFIT Department Code],
SRCE_GFS_DEPT_DE as [PROFIT Department],
MRKTG_PGM_C as [PROFIT Marketing Program Code],
SRCE_PRDCG_BRCH_C as [PROFIT Producing Branch Code],
SRCE_PRDCG_BRCH_DE as [PROFIT Producing Branch Desc],
SRCE_PRDCG_BRCH_DE as [Writing Branch],
SRCE_SERV_BRCH_C as [PROFIT Service Branch Code],
SRCE_SERV_BRCH_DE as [PROFIT Service Branch Desc],
CURY_C as [PROFIT Currency Code],
SRCE_RE_CVR_C as [PROFIT Reinsurance Coverage Code],
'PROFIT' as Source
FROM
[.\Extract Results\PROFIT Premium Details.del]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);