Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Inner Join Problem

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

Champ1Champ2
A1
B2
C3

Table 2

Champ2Champ3
2X
3Y
4Z

What I want to obtain in Qlikview is :

Table 3

Champ1Champ3
BX
CY

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

Champ1Champ2Champ3
B2X
C3Y

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Inner Join Problem

Just drop the field at the end of your script:

DROP FIELD Champ2;

5 Replies
MVP
MVP

Re: Inner Join Problem

Just drop the field at the end of your script:

DROP FIELD Champ2;

Not applicable

Re: Inner Join Problem

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.

MVP
MVP

Re: Inner Join Problem

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.

Not applicable

Re: Inner Join Problem

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

MVP
MVP

Re: Inner Join Problem

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.

Community Browser