Associate the table using new key

[Qlikuser09]

joining the tables using newly assigned keys

I have two sample tables like below

Table 1:

company code	Fields	Key1
AA	Management total	1
AA	staff total	2
AB	Management total	1
AB	staff total	2

Table 2:

Company code	Attributed fields	key 2
AA	Manager	1
AA	CIO	1
AA	IT Staff	2
AA	Dev Staff	2
AB	Manager	1
AB	CIO	1
AB	IT Staff	2
AB	Dev Staff	2

these tables are already joined by the primary Key company code, now I want to associate them based on Key 1 and Key 2

[Qlikuser09]

I have two sample tables like below

Table 1:

company code	Fields	Key1
AA	Management total	1
AA	staff total	2
AB	Management total	1
AB	staff total	2

Table 2:

Company code	Attributed fields	key 2
AA	Manager	1
AA	CIO	1
AA	IT Staff	2
AA	Dev Staff	2
AB	Manager	1
AB	CIO	1
AB	IT Staff	2
AB	Dev Staff	2

these tables are already joined by the primary Key company code, now I want to associate them based on Key 1 and Key 2

[abhijitnalekar]

HI @Qlikuser09 ,

Table1 

Load

Company code & Key1 as Key, 

Fields,

Key1

from xyz.qvd;

Table2;

Load

Company code & Key2 as Key, 

Fields,

Key1

from xyz.qvd;

Hope this help

[abhijitnalekar]

Hi @Qlikuser09 ,

I have attached QVF file for reference.

[Qlikuser09]

thanks for that however for me I'm getting this error when trying to do that.

The following error occurred:

Field 'Key' not found

[abhijitnalekar · Accepted solution]

Can you send the screenshot of the script. The same QVF is working properly.

[Qlikuser09]

BaseTable

Company Code	Attributed fields	Count
AA	Manager	2
AA	CIO	1
AA	IT Staff	1
AA	Dev Staff	1
AA	Management Total	3
AA	Staff Total	2
AB	Manager	5
AB	CIO	2
AB	IT Staff	3
AB	Dev Staff	2
AB	Management Total	7
AB	Staff Total	5

I subdivided this main table into two table(MAinGroup and Subgroup) using load function 

MainGroup:

Load
[CompanyCode] as [CompanyCode],

Atributedfields as Fields,

if(Fields = 'Management Total', 1,

if(Fields = 'Staff Total',2,3) as Key

Resident [BaseTable]

MainGroup:

company code	Fields	Key
AA	Management total	1
AA	staff total	2
AB	Management total	1
AB	staff total	2

SubGroup

Load
[CompanyCode] as [CompanyCode],

Atributedfields as AttributedFields,

if(Attributed fields = 'Manager' or Fields = 'CIO', 1,

if(Attributed fields = 'IT Staff to Attributed fields ='Dev Staff' ,2,3) as FieldID

Resident [BaseTable]

Company code	Attributed fields	Field ID
AA	Manager	1
AA	CIO	1
AA	IT Staff	2
AA	Dev Staff	2
AB	Manager	1
AB	CIO	1
AB	IT Staff	2
AB	Dev Staff	2

Now I want to join these two tables using the fieldID and Key, however these are already joined by company code