Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL Join in QlikView

Hi all,

I know there are a number of posts around this, however, nothing seems to answer my question directly. I am trying to join 4 tables (my data source is a mess, but I can't do much about it) using the below SQL statement, however, when I go and run it in QlikView I get the following error in my log: Field not found: <TypeID>

I am not sure where to go at this point. I have bolded and underlined where I think the script is failing. Any thoughts would be helpful.

SELECT PersonID, Date, li.TypeID as LaborTypeID, LocID, Qty, Amt, lt.ID as LaborTypeID,

     lt.SiteID, lt.Type, Code, Label, lt.ItemID, StandardType, lp.ID, lp.SiteID asEmpSiteID,

     EmpID, Name, lp.Type as LaborPersonType, Status, BadgeID, lp.ItemID as EmpItemID,Address1,

     Address2, City, State, Zip, Phone, Email, SSN, PayType, MaritalStatus, DateBirth,DateStart,

     DateStop, DateModified,NumOfExemptsPersonal, NumOfExemptsOther, IsRehireable,StopReason, it.itemID,

     PID, it.TypeID, itemGLAcct

FROM LaborItems li, LaborTypes lt, LaborPerson lp, Items it

where  li.TypeID = lt.ID

             and li.PersonID = lp.ID

             and lt.itemID = it.ItemID

             and date > '2014-11-01'

3 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this, Qlikview joins the tables automatically based on the common column names, so sometimes we need to give aliases for field names to auto join in qlikview.

LaborItems:

SQL SELECT

*

FROM LaborItems;


LaborTypes:

SQL SELECT

*

FROM LaborTypes;


LaborPerson:

SQL SELECT

*,

ID AS PersonID

FROM LaborPerson;


Items:

SQL SELECT

*

FROM Items;


I am not sure in which table you have Date field, just put


WHERE date > '2014-11-01'

in the corresponding table script.


Hope this helps you.


Regards,

Jagan.


nagaiank
Specialist III
Specialist III

LaborTypeID  is defined twice.

Also, you may add space between 'as' and 'EmpSiteID'

anbu1984
Master III
Master III

PID, it.TypeID, itemGLAcct -- Do you have TypeID in Items table?

Were you able to run this sql directly in database?