topic fieldvalues as key in QlikView

fieldvalues as key

Wed, 08 Apr 2015 13:53:00 GMT

Hi all,

i have 2 tables
RTtype:
Rules,elements
1a ind,(id,lcc)
1b ind,(lcc,cc)
1c ind,(prgnm,cc)

RTentry:
Rules, id,lcc,cc,prgnm
1a ind, 1, a,11,abc
1b ind, 2, c,21,bbf
ic ind, 3, d,23,hhj

now i need to create a column 'key'in RTentry
like below

RTentry:
Rules, id,lcc,cc,prgnm,       key(values of fields that are in element field of RTtype)
1a ind, 1, a,11,abc,           1,a
1b ind, 2, c,21,bbf,           c,21
ic ind, 3, d,23,hhj,           hhj,23

thanks in advance...!

Re: fieldvalues as key

Wed, 08 Apr 2015 14:30:43 GMT

please suggest solution

Re: fieldvalues as key

pokassov — Wed, 08 Apr 2015 14:33:14 GMT

Could you more explain logic for key field?

Re: fieldvalues as key

Wed, 08 Apr 2015 14:40:05 GMT

for example 1a ind have (id,lcc) in elements fied in RTtype table .so based on that combination i need a key filed with values of id,lcc that is 1,a in RTentry, same way for ib ind i have lcc,cc in RTtype than value of key is c,21 and so on....

Re: fieldvalues as key

pokassov — Wed, 08 Apr 2015 14:56:07 GMT

Left Join (RTentry)

LOAD

Rules,

Mid(elements,2,Index(elements,',')-2) as elements1,

PurgeChar(Mid(elements,Index(elements,',')+1,5),' )') as elements2

Resident

RTtype;

Left Join (RTentry)

LOAD

Rules,

if(elements1='id',id,

if(elements1='lcc',lcc,

if(elements1='cc',cc,

if(elements1='prgnm',prgnm

))))&','&

if(elements2='id',id,

if(elements2='lcc',lcc,

if(elements2='cc',cc,

if(elements2='prgnm',prgnm

)))) as key

Resident

RTentry;

drop fields elements1, elements2;

Re: fieldvalues as key

Anonymous — Wed, 08 Apr 2015 15:11:06 GMT

='('&if(mid(elements,2,index(elements,',')-2)='id',id,

if(mid(elements,2,index(elements,',')-2)='lcc',lcc,

if(mid(elements,2,index(elements,',')-2)='cc',cc,

if(mid(elements,2,index(elements,',')-2)='prgnm',prgnm))))

&','

&if(left(mid(elements,index(elements,',')+1),len(mid(elements,index(elements,',')+1))-1)='id',id,

if(left(mid(elements,index(elements,',')+1),len(mid(elements,index(elements,',')+1))-1)='lcc',lcc,

if(left(mid(elements,index(elements,',')+1),len(mid(elements,index(elements,',')+1))-1)='cc',cc,

if(left(mid(elements,index(elements,',')+1),len(mid(elements,index(elements,',')+1))-1)='prgm',prgnm))))&')'

Re: fieldvalues as key

Wed, 08 Apr 2015 15:17:20 GMT

Hi ,

field id,lcc ,cc,prgmn are not static .they may change .so i cannot hard code it

Re: fieldvalues as key

Anonymous — Thu, 09 Apr 2015 03:59:20 GMT

you mean the number of columns you upload keeps changing ?

it means you have loaded it as Load *

Re: fieldvalues as key

Thu, 09 Apr 2015 04:56:37 GMT

yes,number of columns will change

Re: fieldvalues as key

Anonymous — Thu, 09 Apr 2015 05:12:18 GMT

Hi,

I have created a dynamic list box to solve your problem by using 2 variables but that will work only with 1 selection of rule at a time

hope you find this helpful

Re: fieldvalues as key

Thu, 09 Apr 2015 05:51:08 GMT

Hi Bhagirath,

please check the Mapping of data from two tables in app development, that my original problem.

please suggest some solution

thanks .....

Re: fieldvalues as key

pokassov — Thu, 09 Apr 2015 14:43:19 GMT

Hello!

I don't know how many records do you have in the table RTentry.

I've found decision, not perfect for big table but quite enough for small one.

If you have already loaded 2 tables, then you can use the following script:

Left Join (RTentry)

LOAD

Rules,

elements

Resident

RTtype;

for i=0 to NoOfRows('RTentry')-1

LET vRules=Peek('Rules',i,'RTentry');

LET vFormula=Peek('elements',i,'RTentry');

LET vFormula=PurgeChar(vFormula,'()' );

LET vFormula=Replace(vFormula,',','&'',''&');

t1:

LOAD

$(vFormula) as key

Resident

RTentry

Where Rules='$(vRules)';

NEXT i;

DROP Table RTentry;

DROP Field elements from t1;

RENAME Table t1 to RTentry;