Doubt SQL SELECT with inner join Resident Table on same step

[#1]

Dear all,

I have a problem and a don't know how to solve.

I have a resident table and I want to connect to other server with ODBC and do an inner join with the resident table

If I do that on SQL select statement on QV is like this:

LOAD

c1,

c2,

c3,

c4,

c5;

SQL SELECT

P.c1 as c1,

P.c2 as c2,

P.c3 as c3,

P.c4 as c4,

P.c5 as c5

FROM P.T1 P INNER JOIN Q.T1 Q on P.TYPE = Q.TYPE;

But Q.t1 needs to be a resident table, and only I want to load the inner join data, not all the data from t1 and then do the inner join.

Thanks for your help

[deepakk]

Hi,

You can use inner join in below way.

Load

a,b,c resident Test;

inner join

sql a,c,d from table;

Make sure only your key field names are same.

[Miguel_Angel_Baeyens]

Hello,

Do something like the following instead

QT1:LOAD * INLINE [c1, c2, c3, c4, c5, TYPEA, 1, 1, 1, 1, XB, 2, 2, 2, 2, XC, 3, 3, 3, 3, X]; FromDatabase: // not needed, but clarifyingINNER JOIN (QT1) LOAD c1, c2, c3, c4, c5, TYPE;SQL SELECT * FROM P;

You can do the join in the QlikView LOAD part, and that should work (with your data instead my dummy example, of course).

Hope this helps

[#4]

On your example I have several questions.

How to add a a where clause on QT1?

SQL Select will load only the inner join data or will load all the data and then QV does an inner join?

Best regards

[deepakk]

hi,

Writing a where clause for resident table is similar to what we write for sql table.

load * resident Test where A='10';

Inner join in QV works in same fashion as in SQL.

It will retrieve similar data from both the tables.

[Miguel_Angel_Baeyens]

[quote user="Jordi Pérez"]How to add a a where clause on QT1?

First, QlikView, although similar, is not SQL. Anyway, based on the example above, you can do something like

QT1B:LOAD *RESIDENT QT1 WHERE TYPE = 'X';

[quote user="Jordi Pérez"]SQL Select will load only the inner join data or will load all the data and then QV does an inner join?

The example above will pull all records from database. It's QlikView that does the JOIN. To do a join you need at least one field in each of the tables to be joined named alike. It's not a join in the source (as it will be should both tables were in SQL Server).

Hope that helps.