Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

Inner Join

Hello Friends,

I am trying to understand Inner Joins in QlikView so can someone please translate the below simple sql script to QlikView load and select code? Here attendee is a table with alias a and timespend is a table with alias b trying to join the two tables with common customerid field. Thanks.

Sql Code

select a.customerid, b.time

from attendee as a

inner join timespend as b

on a.customerid = b.customerid

where a.customerid > 50;

1 Solution

Accepted Solutions
oknotsen
Master III
Master III

All fields you ask for in the preceding load also have to be mentioned in your SQL statement.

TableA:

LOAD CustomerID,AccountNumber;

SQL Select AccountNumber

      From TableA

      Where Type In ('K','P')

Should be...

TableA:

LOAD CustomerID,AccountNumber;

SQL Select CustomerID, AccountNumber

       From TableA

      Where Type In ('K','P');

Sames goes for your 2nd table.

Note that you also seemed to have missed a semi-colon after your where statement, but that might just have been a copy-paste mistake.

Also check if the table names in the SQL statements are 100% exactly the same as in your database.

May you live in interesting times!

View solution in original post

8 Replies
ramoncova06
Specialist III
Specialist III

from QV help

Inner

The Join and Keep prefixes can be preceded by the prefix inner.

If used before join it specifies that an inner join should be used. The resulting table will thus only contain combinations of field values from the raw data tables where the linking field values are represented in both tables.

If used before keep, it specifies that both raw data tables should be reduced to their common intersection before being stored in QlikView.

inner ( join | keep) [ (tablename ) ](loadstatement |selectstatement )

Examples:

Table1

A

B

1

aa

2

cc

3

ee

Table2

A

C

1

xx

4

yy

QVTable:

SQL SELECT * FROM table1;

inner join SQL SELECT * FROM table2;

QVTable

A

B

C

1

aa

xx

QVTab1:

SQL SELECT * FROM Table1;

QVTab2:

inner keep SQL SELECT * FROM Table2;

QVTab1

A

B

1

aa

QVTab2

A

C

1

xx

The two tables in the keep example are, of course, associated via A.

See also:

Outer

Left

Right

Back to Script Statements and Keywords.

QlikView 11.20 SR6

oknotsen
Master III
Master III

Don't bother with limiting your data from your source; you want to do business discovery, so get everything

If you would do the join in SQL, you told us this is your script:

select a.customerid, b.time

from attendee as a

inner join timespend as b

on a.customerid = b.customerid;

To do the same thing in QlikView, do this:

TableA:

select customerid

from attendee;

TableB:

INNER JOIN(TableA):

select customerid, time

from timespend;

QlikView will see the fields the tables have in common.

You can add your where clause in either of the statements if you like.

May you live in interesting times!
star_limit
Creator II
Creator II
Author

Hi,

Is it possible you can give me an idea using this example on what needs to go in the Load statement? And, how I can avoid the synthetic keys?

Thank you.

oknotsen
Master III
Master III

Synthetic keys will be created after the complete script has run, so they will not be created if you are joining tables with multiple fields combined being the keys.

The above will work without preceding loads, but here is the script with them:

TableA:

LOAD customerid;

select customerid

from attendee;

TableB:

INNER JOIN(TableA):

LOAD customerid, time;

select customerid, time

from timespend;

May you live in interesting times!
star_limit
Creator II
Creator II
Author

I tried the below code but it says CustomerID not found..then table not found.

TableA:

LOAD CustomerID,AccountNumber;

SQL Select AccountNumber

       From TableA

      Where Type In ('K','P')

TableB:

INNER JOIN(TableA)

Load CustomerID,QID,Response, Time(Time#(Response,'hhmm') as FormattedTime;

SQL SELECT CustomerID, Response

       FROM TableB

      Where QID = 'T.PIP';

I want to see AccountNumber and FormattedTime in Table Box.

Thank you.

oknotsen
Master III
Master III

All fields you ask for in the preceding load also have to be mentioned in your SQL statement.

TableA:

LOAD CustomerID,AccountNumber;

SQL Select AccountNumber

      From TableA

      Where Type In ('K','P')

Should be...

TableA:

LOAD CustomerID,AccountNumber;

SQL Select CustomerID, AccountNumber

       From TableA

      Where Type In ('K','P');

Sames goes for your 2nd table.

Note that you also seemed to have missed a semi-colon after your where statement, but that might just have been a copy-paste mistake.

Also check if the table names in the SQL statements are 100% exactly the same as in your database.

May you live in interesting times!

View solution in original post

star_limit
Creator II
Creator II
Author

That worked like a charm. Thank you so much for explaing this.

oknotsen
Master III
Master III

Happy I could help .

Have a great weekend!

May you live in interesting times!