Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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'

Tags (2)
3 Replies
jagan
Not applicable

Re: SQL Join in QlikView

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
Not applicable

Re: SQL Join in QlikView

LaborTypeID  is defined twice.

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

anbu1984
Not applicable

Re: SQL Join in QlikView

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

Were you able to run this sql directly in database?