Left Join using SQL Select script

[Ruthie09]

It's late and my brain is fried.  Please help.

I have a SQL Select query in Qlik Sense that manipulates site location data.  I've named that, not surprisingly, LocationData.

I have another SQL Select query that manipulates help desk tickets called Incidents.  I want to do a left join on Incidents to pull in the caller's location data.  This is a totally basic thing in SQL and I have the syntax all done.  I just can't figure out how to get my "FROM" section to use the LocationData table I created before my Incidents table (rather than look for it in the database I'm primarily using).

I know if has something to do with Resident, Preceding, Keep and/or Join stuff but I haven't been able to make sense of that.

I don't know if that made any sense.  Let me know if you need to see code.

Help?

[dwforest]

Joins in Qlik do not work the same as in SQL, the join is "automatic" based on matching field name and can only join on one field. 

LOAD A, B from table1;

JOIN LOAD A, C from table2;

The above will yield on Qlik table with A,B,C

[Ruthie09]

But that's the problem.  I don't want an automatic join because it will pull in all the records from the LocationData table, many of which do not have incidents associated with them.  I need all records from Incidents and the Country and Region information associated with the incident locations (thus I need to left join LocationData to Incidents).  I don't want rows that contain locations (call it Table B) if there is no incident using that location.

The Incidents query will pull most of its data from the same library connection as the LocationData will, but the Incidents query needs to also pull Country and Region from the LocationData table.

Also, as I mentioned, I am using only SQL Select statements, not Load.

See if this helps:

LIB CONNECT TO 'MarkLogic-PA-Dev'

LocationData:

SELECT

a.NAME as LocName,

a.SYS_ID as LocSysId,

a.DV_U_REGION as LocRegion,

a.COUNTRY as LocCountry

FROM PA.CMN_LOCATION a;

Incidents:

SELECT

a.INC_NUMBER as IncNumber,

a.INC_DV_LOCATION as IncLocation,

n.LocRegion as IncRegion,

n.LocCountry as IncCountry

FROM PA.INCIDENT_AFFECTED_CIS a

LEFT JOIN LocationData n on a.INC_LOCATION_ID = n.LocSysID;

[Or]

Firstly, there's really no reason not to use a Load statement (and I believe one is implicitly added if you don't write one in). You can just Load * if you don't want to change anything and it's easier to read and/or modify later.

If you want to left-join locations to incidents without adding load statements, you will need to load the incidents first, then the locations with a left-join. This will still return the entire dataset from the underlying SQL database, but Qlik will then reduce the values to those that are actually in Incidents. Joining is always done on fields with matching names, so if you want to avoid a specific field being part of the join, just rename it to something else on one side of the join. Likewise, if you want a field to be part of the join when it has a different name originally, rename both sides so that they match.

If you want to avoid loading these values from the underlying database entirely, you will have to write the join into the actual queries.

[Apprentice4life]

It is possible to join tables in Qlik. You will need to rename the two fields you need to join to the same field names. Once you do the JOIN (Doesn't matter if it is LEFT JOIN or INNER JOIN), there will only be one data table in the Qlik model.  In your case, it is better to keep as two separate tables. Rename the common field in Table A and B to the same name and let Qlik do the join automatically.

Are there some syntax errors in your SQL table? Both tables use alias 'a', are the fields labelled with alias 'n' coming from a third table?

Example:

[Incident]:

LOAD
Field1 as IncID,
Field2 as Location,
Field3 as IncRegion,
Field4 as IncCountry;

SQL SELECT *
FROM SQLTableName1;

[LocationData]:

LOAD
Field1 as LocSysID,
Field2 as Location,
Field3 as LocRegion,
Field4 as LocCountry;

SQL SELECT *
FROM SQLTableName2;

Hope this helps!

[Ruthie09]

The load editor actually has 4 separate queries, LocationData and Incidents only being two of them.  Each query's FROM statement starts with "a" as the alias and goes alphabetically from there.

The Incidents query has about 15 joins so it happens that the example snippet I used is at alias "n".  Most are simple left joins and reference tables in the database library.  This is the only one where I chose to bring in one of the tables I was joining Incidents to so that I could manipulate it.

Clearly I need to do more reading because I'm still not getting it.  In the meantime, I've found a work-around.  It's probably not the most efficient way of dealing with the info but it gets the job done.  😞