topic joining 2 fields from different tables in load script in QlikView

joining 2 fields from different tables in load script

Tue, 13 Jul 2010 16:55:04 GMT

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

joining 2 fields from different tables in load script

Tue, 13 Jul 2010 17:11:26 GMT

What is a relationship between Table1 and Table2. Or do you want to make cartesian?

joining 2 fields from different tables in load script

Tue, 13 Jul 2010 17:44:20 GMT

i guess cartesian join, i havent defined any joins between table 1 and table 2.

joining 2 fields from different tables in load script

Wed, 14 Jul 2010 10:00:18 GMT

any update on this thread??????

joining 2 fields from different tables in load script

syed_muzammil — Wed, 14 Jul 2010 13:39:50 GMT

Hi,

TABLE 2
LOAD
FIELD A,
FIELD B,
FIELD C,

FIELD 1& '.' & FIELD B

FROM data\Table 2.qd (qvd);
RESIDENT TABLE 1;

The '.' in

FIELD 1& '.' & FIELD B is a delimiter. You can remove it if you want to.

joining 2 fields from different tables in load script

Wed, 14 Jul 2010 14:11:50 GMT

Normal 0 false false false MicrosoftInternetExplorer4

Hi, this doesnt seem to work as i get the 'Field not found - <Transaction Type> ' error.

Heres the script im using which shows tables 1 and 2. The key field im trying to create is [Transaction Type]& '.' & LEDTYP_ID

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

Directory;

Transactions:

LOAD TRX_ID AS [Transaction ID],

[Transaction Type],

ACC_ID,

FROM

data\TRANSACTIONS.qvd

(qvd);

LOAD

ACC_ID,

LEDTYP_ID,

[Transaction Type]& '.' & LEDTYP_ID

FROM

data\ACCOUNTS.qvd

(qvd);

RESIDENT TRANSACTIONS;

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

What do i have to do to get this script to work and make the join field?

joining 2 fields from different tables in load script

syed_muzammil — Wed, 14 Jul 2010 14:32:59 GMT

hey,

Try this . You have to give an Alias Name to the new feild created. [Transaction Type]& '.' & LEDTYP_ID AS KEY

Transactions:

LOAD TRX_ID AS [Transaction ID],

[Transaction Type],

ACC_ID,

FROM

data\TRANSACTIONS.qvd

(qvd);

LOAD

ACC_ID,

LEDTYP_ID,

[Transaction Type]& '.' & LEDTYP_ID AS KEY

FROM

data\ACCOUNTS.qvd

(qvd);

RESIDENT TRANSACTIONS;

joining 2 fields from different tables in load script

Wed, 14 Jul 2010 14:40:34 GMT

The following errors appear

Field not found - <Transaction Type>

also

Unknown statement
RESIDENT Transactions

joining 2 fields from different tables in load script

Wed, 14 Jul 2010 18:43:32 GMT

Any help on this would be great?

joining 2 fields from different tables in load script

Fri, 16 Jul 2010 15:29:39 GMT

does anyone have any advise on this?

regards

jason

joining 2 fields from different tables in load script

syed_muzammil — Sat, 17 Jul 2010 08:16:37 GMT

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;

joining 2 fields from different tables in load script

Sat, 17 Jul 2010 11:16:56 GMT

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

joining 2 fields from different tables in load script

syed_muzammil — Sat, 17 Jul 2010 11:42:13 GMT

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;

joining 2 fields from different tables in load script

Sat, 17 Jul 2010 11:55:47 GMT

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.