Keytable composite key

[bobbydave]

Concatenate (KeyTable)

Load %Product_detail,

      COUNTRY_CODE_SUM                  as %Country,

      MakeDate(left(ACC_PERIOD_SUM,4),Right(ACC_PERIOD_SUM,2))       as %Calendar,

      AGENT_ID_SUM &' - '& AGENT_NAME_SUM             as AGENT_DTL_SUM,

       ApplyMap('MapGMSScheme', COUNTRY_CODE_SUM & '-' & PRODUCT_SUM & '-' & AGENT_ID_SUM, 'Non-GMS')     as Result       

Resident PolicySummary;

The result of AGENT_DTL_SUM is in the picture above. I am trying to remove the duplicate so i am wondering how could you ensure that the AGENT_DTL_SUM has both a number-text and not just number- and not just -text?

[OmarBenSalem]

This is a crappy solution, but try to add a condition :

Concatenate (KeyTable)

LOAD * where len(trim(Result)) > 12;

Load %Product_detail,

      COUNTRY_CODE_SUM                  as %Country,

      MakeDate(left(ACC_PERIOD_SUM,4),Right(ACC_PERIOD_SUM,2))       as %Calendar,

      AGENT_ID_SUM &' - '& AGENT_NAME_SUM             as AGENT_DTL_SUM,

       ApplyMap('MapGMSScheme', COUNTRY_CODE_SUM & '-' & PRODUCT_SUM & '-' & AGENT_ID_SUM, 'Non-GMS')     as Result      

Resident PolicySummary;

[bobbydave]

I see what you were trying to do, but no, tthis doesnt work.

I tried

if(len(AGENT_ID_SUM)>0, NULL(), AGENT_ID_SUM) & '-' &

if(len(AGENT_NAME_SUM)>0, NULL(), AGENT_NAME_SUM)  AS AGENT_DTL_SUM

but alas, no joy either

[stabben23 · Accepted solution]

Hi David,

This is normal behavior when concatenate tables.

One way could be to add a "flag" for each table you concatenate.

Concatenate (KeyTable)

Load %Product_detail,

      COUNTRY_CODE_SUM                  as %Country,

      MakeDate(left(ACC_PERIOD_SUM,4),Right(ACC_PERIOD_SUM,2))       as %Calendar,    

      AGENT_ID_SUM &' - '& AGENT_NAME_SUM             as AGENT_DTL_SUM,

       ApplyMap('MapGMSScheme', COUNTRY_CODE_SUM & '-' & PRODUCT_SUM & '-' & AGENT_ID_SUM, 'Non-GMS')     as Result,

'ThisTable' as Table      

Resident PolicySummary;

and in KeyTable add 'KeyTable' as Table

Then in Expression use {<Table={'ThisTable'}>} to sort them out.

Another  way is to create the same composite key in the KeyTable.

[bobbydave]

i ended up just saying if the length of AGENT_DTL_SUM was >6, then show me AGENT_DTL_SUM in my expression.

I just wanted to tidy it up in the script so that i could use the cleaned up version in the expression but i can live with the workaround