joining 2 fields from different tables in load script

[#1]

i want to create a unique key in a table so that qlikview automatically joins the tables.

Therefore how do i join 2 fields from 2 different tables in the load script:

MY SCRIPT LOOKS LIKE THE FOLLOWING

---------

TABLE 1
LOAD
FIELD 1,
FIELD 2,
FIELD 3,
FROM data\Table 1.qvd (qvd);

TABLE 2
LOAD
FIELD A,
FIELD B,
FIELD C,
FROM data\Table 2.qvd (qvd);

TABLE 3
LOAD
Key,
[Account Type]
[Transaction Type]
FROM [data\Lookup Table 3.xls];

------------

What i want to do is create a new field in Table 2 called 'Key' and this should be made from a concatenation of Table 1.Field 1 + Table 2.Field B

Regards

[syed_muzammil]

Hi,

Sorry Resident load Cannot be used with From Clause.

Try this now. I hope this wud finally work for u.

Temp1:
LOAD * INLINE [
A, B, C
1, a, 11
2, b, 22
3, c, 33
4, d, 44
];

Temp2:
LOAD * INLINE [
D, E, A
x, 111, 1
y, 222, 3
z, 333, 2
];

Temp3:
load
A,
B as b,
C as c
Resident Temp1;

Left Join(Temp3)
load A,
D as d,
E as e
Resident Temp2;

Left Join (Temp2)
Load
A,
b&'.'&d as Key
Resident Temp3;
Drop table Temp3;

[#3]

Hi whats the logic behind this?

why do so many temp tables have to be created and then dropped?

Also im a little confused with your naming of tables as temp1. temp2 when they are called Table1 Table 2.

Thanks

[syed_muzammil · Accepted solution]

Hi,

This is just an example for u . You can replace the Temp1 and Temp2 tablea with your Table1 and Table2. Temp3 is the temporary table. Sorry for the confusion. Heres the code as per your requirement.

There is jus one temporary table Main_temp .

Transactions:
LOAD TRX_ID AS [Transaction ID],
[Transaction Type],
ACC_ID,
FROM
data\TRANSACTIONS.qvd
(qvd);

Accounts:
LOAD
ACC_ID,
LEDTYP_ID,
FROM
data\ACCOUNTS.qvd
(qvd);

Main_Temp:
load
ACC_ID,
[Transaction Type] as TT,
Resident Transactions;

Left Join(Main_Temp)
load ACC_ID,
LEDTYP_ID as LTI
Resident Accounts;

Left Join (Accounts)
Load
ACC_ID,
TT&'.'&LTI as Key
Resident Main_Temp;
Drop table Main_Temp;

[#5]

hi syed,

many thanks for the clarification. I added the following code:

--------------

Main_Temp:
load
ACC_ID,
[Transaction Type] as TT
Resident Transactions;

Left Join(Main_Temp)
load ACC_ID,
LEDTYP_ID as LTI
Resident Accounts;

Left Join (Accounts)
Load
ACC_ID,
TT&'.'&LTI as Key
Resident Main_Temp;
Drop table Main_Temp;

-----------

This worked a treat and created a unique key into tables Accounts and Transactions.( see attached picture)

Many thanks for you help with this Syed.