topic Re: SQL Joins in App Development

SQL Joins

Anonymous — Sat, 22 Oct 2016 17:24:08 GMT

Hello All

Please help me in the below queries.

Query1. I have the SQL table and Excel file. I want a left join between them based on three lookups. But when i am sourcing it in SQL Query it shows an error. My main issue is that I want a join in SQL Table not by making a composite key. I want it in below way as I have three lookups

LIB CONNECT TO 'TIGUNVMBGWL0011';

AccountTemp:

LOAD

PGLD,

CLN,

DIN AS DAN,

MAN AS MN,

ALID

FROM [lib://Files/AT.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD CCD,

CIN,

DIN,

MAN,

ACLID;

SQL SELECT

CCD,

CIN,

DIN,

MAN,

ACLID

FROM tempdb.dbo."Case" C

LEFT JOIN AccountTemp A ON A.CLN = C.CIN AND A.DIN = C.DAN AND A.MAN = C.MN ;

Query 2: Is it possible two make a join of two resident table based on three lookups with where clause.

As for E.g.

I have two resident table Account & Case

MAIN:

LOAD *

RESIDENT ACCOUNT A

LEFT JOIN CASE B ON A.A = B.B AND A.C = B.D AND A.E = B.F

WHERE A.A IS NOT NULL;

Please suggest.

Re: SQL Joins

swuehl — Sat, 22 Oct 2016 17:51:08 GMT

The SQL SELECT of your first query is sent to the SQL driver and executed by the driver / you DBMS.

Your resident table AccountTemp is part of the Qlik resident data model and unknown outside Qlik system, i.e. unknown to your SQL driver.

You need to join both tables within Qlik, using JOIN LOAD prefixes.

Within Qlik, you implicitely define the key fields for your JOIN by using same field names in both tables to join (remember to name every other field not to join differently in both tables).

If you want to apply a WHERE clause to either table, you can do this.

Re: SQL Joins

Anonymous — Sun, 23 Oct 2016 06:29:34 GMT

Thanks Sir.. I got your point. I have a question related to above one.

Please help me to resolve that query. I need it on urgent basis.

Thanks a lot

Regards

SQL Running