Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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.
LaborTypeID is defined twice.
Also, you may add space between 'as' and 'EmpSiteID'
PID, it.TypeID, itemGLAcct -- Do you have TypeID in Items table?
Were you able to run this sql directly in database?