Script error / Synthetic keys

[priyarane]

Hi Community,

The below script is creating Syntheteys, I gues as script is not ending.

Problem with - 'if(TABLE_NAME = 'RETAIL_EXPOSURE', 'NA|' & LOCAL_GL_BAL_KEY,  EXPOSURE_ATTRIBUTE_2 & '|NA') AS GL_BAL_KEY', if I remove this one then script is going good.

right now I have 2 CDM_T_CDR file like CDM_T_CDR_30Sep and CDM_T_CDR_30Oct, If I go with only one then Script is going good.

below is the script as we are going right now:

First 10
GL_MAP:
Mapping LOAD EXPOSURE_ATTRIBUTE_2,
     LOCAL_GL_BAL_KEY  
FROM
[\\QVDs\TRN\TRN_V_HNAH_GL_RECON_RETAIL_MRDM_BS_*.qvd]
(qvd);

T_CDR:
First 10
LOAD * ,
if(TABLE_NAME = 'RETAIL_EXPOSURE', 'NA|' & LOCAL_GL_BAL_KEY,  EXPOSURE_ATTRIBUTE_2 & '|NA') AS GL_BAL_KEY;
First 10
LOAD *,
ApplyMap('GL_MAP',EXPOSURE_ATTRIBUTE_2,'') as LOCAL_GL_BAL_KEY,
ApplyMap('SBLC_TYPE_MAP',CONTRACT_REFERENCE,'') as SBLC_TYPE,
ApplyMap('CRC_RANK_MAP',INCORPORATION_COUNTRY,'') as CRC_RANK,
ApplyMap('OECD_FLAG_MAP',INCORPORATION_COUNTRY,'') as OECD_FLAG

From [\\QVDs\CDM\CDM_T_CDR_*.qvd] (qvd)
Where WildMatch(CNTX_DESCRIPTION,'*FED*') > 0;

[Anonymous]

cud you post some sample data?

[swuehl]

I assume this is not the complete LOAD script, is it?

Could you post the document log?

[priyarane]

Yes script is not completing.

logs like fletched data thats it...

[priyarane]

Can we modify the below bold script

T_CDR:

First 10

LOAD * ,

if(TABLE_NAME = 'RETAIL_EXPOSURE', 'NA|' & LOCAL_GL_BAL_KEY,  EXPOSURE_ATTRIBUTE_2 & '|NA') AS GL_BAL_KEY;

First 10

LOAD *,

ApplyMap('GL_MAP',EXPOSURE_ATTRIBUTE_2,'') as LOCAL_GL_BAL_KEY,

ApplyMap('SBLC_TYPE_MAP',CONTRACT_REFERENCE,'') as SBLC_TYPE,

ApplyMap('CRC_RANK_MAP',INCORPORATION_COUNTRY,'') as CRC_RANK,

ApplyMap('OECD_FLAG_MAP',INCORPORATION_COUNTRY,'') as OECD_FLAG

From [\\QVDs\CDM\CDM_T_CDR_*.qvd] (qvd)

Where WildMatch(CNTX_DESCRIPTION,'*FED*') > 0;

[Anonymous]

Try this:

if(TABLE_NAME = 'RETAIL_EXPOSURE', 'NA|&'-'&LOCAL_GL_BAL_KEY', 

'EXPOSURE_ATTRIBUTE_2&'-'&|NA') AS GL_BAL_KEY;

[Peter_Cammaert · Accepted solution]

Your problem is probably situated in the combination of a LOAD from a wildcard file specification with a Preceding LOAD that rearranges columns in the final table. Those two do not work well together (one of those QV anomalies)

Try with this code:

:

Temp_T_CDR:

First 10

LOAD *,

ApplyMap('GL_MAP',EXPOSURE_ATTRIBUTE_2,'') as LOCAL_GL_BAL_KEY,

ApplyMap('SBLC_TYPE_MAP',CONTRACT_REFERENCE,'') as SBLC_TYPE,

ApplyMap('CRC_RANK_MAP',INCORPORATION_COUNTRY,'') as CRC_RANK,

ApplyMap('OECD_FLAG_MAP',INCORPORATION_COUNTRY,'') as OECD_FLAG

From [\\QVDs\CDM\CDM_T_CDR_*.qvd] (qvd)

Where WildMatch(CNTX_DESCRIPTION,'*FED*') > 0;

T_CDR:

NOCONCATENATE

LOAD *,

if (TABLE_NAME = 'RETAIL_EXPOSURE', 'NA|' & LOCAL_GL_BAL_KEY,  EXPOSURE_ATTRIBUTE_2 & '|NA') AS GL_BAL_KEY

RESIDENT Temp_T_CDR;

DROP Table Temp_T_CDR;

:

Best,

Peter