Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
deepanshu_chamo1
Not applicable

SQL Joins

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.

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: SQL Joins

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.

2 Replies
swuehl
Not applicable

Re: SQL Joins

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.

deepanshu_chamo1
Not applicable

Re: SQL Joins

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