topic how to get second occurrence value from mapping table? in QlikView

how to get second occurrence value from mapping table?

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

Hi,

I have a mapping table like following

mapping load

short name,long name

KA,karnataka

KA,karnataka1

TN,Tamilnadu

TN,Tamilnadu1

AP,Andhra pradesh

AP,Andhrapradesh1

using applymap I want out put from the mapping table is

long name

karnataka1

Tamilnadu1

Andhrapradesh1

Thanking you,

Anji

Re: how to get second occurrence value from mapping table?

tresB — Fri, 31 Mar 2017 06:23:59 GMT

Try something like:

Map:

mapping load "short name","long name" where Occurance=2;

Load *, AutoNumber(RecNo(),"short name") as Occurance

Inline [ "short name","long name"

KA,karnataka

KA,karnataka1

TN,Tamilnadu

TN,Tamilnadu1

AP,Andhra pradesh

AP,Andhrapradesh1];

Load

ApplyMap('Map',"name") as Mapped

Inline [

name

]

Re: how to get second occurrence value from mapping table?

Peter_Cammaert — Fri, 31 Mar 2017 06:50:00 GMT

Keep in mind that a mapping table only stores a single translation value (from the 2nd column) per distinct index value (1st column). There simply are no two values in your mapping table for [short name]=KA.

BTW for every distinct value in the first column, a mapping table only keeps the first translation value loaded ! All subsequent "corrections" to a mapping table entry will be ignored. Only the new values will be added.