Key field issue -

[BI_Dev]

I have 2 tables
Table 1:

Name1  Name 2

Sam      Sammy

Matt       Matty

Table 2:

Name 

Sam

Matty

Name is the Key field in Table 2 and in Table 1 I want to be able to use both Name1 & Name 2 based on the name match in Table 2.

Here Sam in Table 1 matches with Table 2 Name and Matty from Name 2 column matches with Name in Table2.

I don't want to concatenate as I have 100 diff fields in both the tables...

Thank you.

[Chanty4u]

Try this 

Table1_Transformed:

LOAD 

    Name1 AS Name, 

    Name1 AS OriginalName, 

    'Name1' AS SourceField

RESIDENT Table1;

LOAD 

    Name2 AS Name, 

    Name2 AS OriginalName, 

    'Name2' AS SourceField

RESIDENT Table1;

[morgankejerhag]

If you want to handle it only within Qlik you probably have to use the concatenation method, but you can filter the rows to those values that are in Table2.

Table2:
Load
Name,
OtherColumns
from [lib://Space:DataFiles/Table2.qvd] (qvd);

Table1:
Load distinct
Name1 as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,Name1);

concatenate (Table1)
Load
Name2 as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,Name2);

If the fields are Name1 to Name100 then you could do a loop

Table1:
Load distinct
Name1 as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,Name1);

for i=2 to 100

concatenate (Table1)
Load
Name$(i) as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,Name$(i));

next

If the names are not Name1, Name2 etc then you could still do a loop over a list of the names that are used

Table1:
Load distinct
XXX as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,XXX);

for each vField in 'YYY','ZZZ'

concatenate (Table1)
Load
$(vField) as Name,
OtherColumns
from [lib://Space:DataFiles/Table1.qvd] (qvd) where exists(Name,$(vField));

next

[henrikalmen]

I'm thinking something like this:

left join(Table1) load Name1, Name2, Name1&'_'&Name2 as namecombined resident Table1;

namelink: load distinct namecombined, Name1 as Name resident Table1;
concatenate (namelink) load distinct namecombined, Name2 as Name resident Table1;