Inner Join Problem

[#1]

Hi,

I'm trying to join 2 tables in a database. I don't want to join them using the INNER JOIN clause of the database but instead I want to use the Inner Join clause of qlikview

the tables are as follow

Table 1

Champ1	Champ2
A	1
B	2
C	3

Table 2

Champ2	Champ3
2	X
3	Y
4	Z

What I want to obtain in Qlikview is :

Table 3

Champ1	Champ3
B	X
C	Y

I tried doing it with the following instructions :

[Table3]:

LOAD

    *;   

SQL SELECT

    [Champ1],

    [Champ2]

FROM

    AdventureWorksDW2012.dbo.[Table1];

Inner Join ([Table3])

LOAD

    *;

SQL SELECT

    [Champ2],

    [Champ3]

FROM

    AdventureWorksDW2012.dbo.[Table2];

The problem is that I get the following result :

Table 3

Champ1	Champ2	Champ3
B	2	X
C	3	Y

And I don't want the column named champ2 (the join key is useless for me)

I tried with the following instruction :

[Table3]:

LOAD

    [Champ1],

    [Champ3];   

SQL SELECT

    [Champ1],

    [Champ2]

FROM

    AdventureWorksDW2012.dbo.[Table1];

Inner Join ([Table3])

LOAD

    *;

SQL SELECT

    [Champ2],

    [Champ3]

FROM

    AdventureWorksDW2012.dbo.[Table2];

but I get the following error :

Field not found - <Champ3>

SQL SELECT

    [Champ1],

    [Champ2]

FROM

    AdventureWorksDW2012.dbo.[Table1]

I anyone can help it would be nice of him.

thanks

[swuehl · Accepted solution]

Just drop the field at the end of your script:

DROP FIELD Champ2;

[#3]

Hi,

It does work, but what if I made a join on 7 fields for example, I would have to drop the 7 fields which is a bit cumbersome, isn't there a way to select only the fields I want instead of dropping those I don't want.

Thank you.

[swuehl]

It would probably be nice if QV syntax were closer to SQL, but it doesn't allow to join already the input tables, then project the fields you want to see only.

Like in SQL:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name=table2.column_name;

Instead, you join a resident table with an output table of a second load (like in your example Table3 with the output from Table2 load). And, QV determines the keys just by looking at same field names in both tables, you can't explicitely state the key fields for the join.

Two alternatives to what I proposed above that came to my mind:

a) Do a resident load of your Table3 with the fields you want, then drop Table3:

[Table3]:

LOAD

    *; 

SQL SELECT

    [Champ1],

    [Champ2]

FROM

    AdventureWorksDW2012.dbo.[Table1];

Inner Join ([Table3])

LOAD

    *;

SQL SELECT

    [Champ2],

    [Champ3]

FROM

    AdventureWorksDW2012.dbo.[Table2];

Result:

LOAD Champ1, Champ3 RESIDENT Table3;

DROP TABLE Table3;

b) JOIN in your SQL DB.

[#5]

Hi,

Thank you for your answer.

For the sake of simplicity I used the same database for the example above, but in reality the two databases are not on the same instance and I can't use database links (it is forbidden by security policies), so I can't make the join at the database level.

Concerning your answer a) this is exactly what I was looking for, the only thing that I'm afraid is the behavior of qlikview memory wise. Say the size of Table3 is 3GB (compressed) and the size of Table3 (with only Champ1 and Champ3) is 2GB, is it possible that at a given point in time Qlikview will use 5GB or ram.

Thanks

[swuehl]

You can use a temporary qvd file if memory is an issue.

I still think dropping the fields is probably the best solution. You can look into creating a composite key for the joining tables, so you need only one field to drop.