topic Re: Key field issue - in App Development

Key field issue -

BI_Dev — Wed, 05 Mar 2025 13:59:28 GMT

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.

Re: Key field issue -

Chanty4u — Wed, 05 Mar 2025 17:18:40 GMT

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;

Re: Key field issue -

morgankejerhag — Thu, 06 Mar 2025 09:58:34 GMT

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));

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));

Re: Key field issue -

henrikalmen — Thu, 06 Mar 2025 10:00:56 GMT

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;