topic Doubt SQL SELECT with inner join Resident Table on same step in QlikView

Doubt SQL SELECT with inner join Resident Table on same step

Fri, 17 Dec 2010 09:32:21 GMT

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

Doubt SQL SELECT with inner join Resident Table on same step

deepakk — Fri, 17 Dec 2010 09:36:40 GMT

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.

Doubt SQL SELECT with inner join Resident Table on same step

Miguel_Angel_Baeyens — Fri, 17 Dec 2010 09:39:36 GMT

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

Doubt SQL SELECT with inner join Resident Table on same step

Fri, 17 Dec 2010 09:56:46 GMT

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

Doubt SQL SELECT with inner join Resident Table on same step

deepakk — Fri, 17 Dec 2010 10:14:18 GMT

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.

Doubt SQL SELECT with inner join Resident Table on same step

Miguel_Angel_Baeyens — Fri, 17 Dec 2010 10:28:12 GMT

[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.