topic Re: Keytable composite key in QlikView

Keytable composite key

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Keytable composite key

OmarBenSalem — Tue, 28 Nov 2017 12:29:33 GMT

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;

Re: Keytable composite key

bobbydave — Tue, 28 Nov 2017 13:35:29 GMT

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

Re: Keytable composite key

stabben23 — Tue, 28 Nov 2017 13:52:00 GMT

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.

Re: Keytable composite key

bobbydave — Tue, 28 Nov 2017 14:11:15 GMT

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