topic Re: Inner Join in QlikView

Inner Join

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

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;

Re: Inner Join

ramoncova06 — Fri, 03 Apr 2015 17:22:52 GMT

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.

Re: Inner Join

oknotsen — Fri, 03 Apr 2015 17:28:23 GMT

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.

Re: Inner Join

Anonymous — Fri, 03 Apr 2015 18:28:27 GMT

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.

Re: Inner Join

oknotsen — Fri, 03 Apr 2015 18:34:06 GMT

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;

Re: Inner Join

Anonymous — Fri, 03 Apr 2015 19:28:39 GMT

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.

Re: Inner Join

oknotsen — Fri, 03 Apr 2015 19:43:37 GMT

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.

Re: Inner Join

Anonymous — Fri, 03 Apr 2015 20:06:55 GMT

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

Re: Inner Join

oknotsen — Fri, 03 Apr 2015 20:10:28 GMT

Happy I could help .

Have a great weekend!

topic Re: Inner Join in QlikView

Inner Join

Re: Inner Join

See also:

Re: Inner Join

Re: Inner Join

Re: Inner Join

Re: Inner Join

Re: Inner Join

Re: Inner Join

Re: Inner Join