Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
from QV help
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.
Back to Script Statements and Keywords.
QlikView 11.20 SR6
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.
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.
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;
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.
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.
That worked like a charm. Thank you so much for explaing this.
Happy I could help .
Have a great weekend!