topic Re: Script error / Synthetic keys in QlikView

Script error / Synthetic keys

priyarane — Mon, 16 Nov 2015 11:28:03 GMT

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;

Re: Script error / Synthetic keys

Anonymous — Mon, 16 Nov 2015 11:38:22 GMT

cud you post some sample data?

Re: Script error / Synthetic keys

swuehl — Mon, 16 Nov 2015 11:42:18 GMT

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

Could you post the document log?

Re: Script error / Synthetic keys

priyarane — Mon, 16 Nov 2015 12:09:26 GMT

Yes script is not completing.

logs like fletched data thats it...

Re: Script error / Synthetic keys

priyarane — Mon, 16 Nov 2015 12:10:28 GMT

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;

Re: Script error / Synthetic keys

Anonymous — Mon, 16 Nov 2015 12:17:38 GMT

Try this:

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

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

Re: Script error / Synthetic keys

Peter_Cammaert — Mon, 16 Nov 2015 12:53:43 GMT

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