Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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