Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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;
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
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.
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