topic Re: SQL Join in QlikView in QlikView

SQL Join in QlikView

Thu, 11 Dec 2014 03:12:20 GMT

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'

Re: SQL Join in QlikView

jagan — Thu, 11 Dec 2014 03:35:22 GMT

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.

Re: SQL Join in QlikView

nagaiank — Thu, 11 Dec 2014 03:54:49 GMT

LaborTypeID is defined twice.

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

Re: SQL Join in QlikView

anbu1984 — Thu, 11 Dec 2014 05:33:56 GMT

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

Were you able to run this sql directly in database?