fieldvalues as key

[#1]

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...!

[#2]

please suggest solution

[pokassov]

Hi

Could you more explain logic for key field?

[#4]

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....

[pokassov]

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;

[Anonymous]

='('&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))))&')'

[#7]

Hi ,

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

[Anonymous]

you mean the number of columns you upload keeps changing ?

it means you have loaded it as  Load *

[#9]

yes,number of columns will change

[Anonymous]

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