Qlikview joins that doesn't join on all matching column names

I'm new to Qlikview and looking for some answers regarding scripting. How can I create Qlikview joins that just join on a specific column (and not all that are having a matching name)? Let's say that I'm having the following tables:

Employee

Id     |    Person   |    DepartmentID   |    Flags
1000   ,    Bob      ,      2001         ,        100000开发者_如何学编程0
1001   ,    Sue      ,      2002         ,        1100000

Department

Id    |    Name    |    Flags
2001  ,    HR      ,    01101111
2001  ,    R&D      ,    1100000

What is the best way of joining those tables on the DepartmentID <-> ID field? The data is provided by SQL selects. I'm thinking of writing SQL views using unique names would be one idea, but there must be a simpler way. Please advise.

---

Karl

First, you really will be better off using the QlikCommunity for these questions, the forum is very well supported by the QlikView user community and you'll get answers quicker, the only reason I found this is that I have a google alert on QlikView.

To your question:

QlikView will automatically create a join on all matching fields and there is no way to stop it doing this, there is also no way to make it join using fields of different names. The answer therefore is to rename your fields in either the SQL Select statement or in the LOAD statement, for example:

Employee: LOAD ID AS EmpID, Person, DepID, Flags AS Emp_Flags; SQL SELECT ID, Person, DepartmentID as DepID, Flags FROM .........;

Department: LOAD ID AS DepID, Name AS DepartmentName, Flags AS Dep_Flags; SQL SELECT ID, Name, Flags FROM .........;

This should do the trick for you.

One more piece of advice, although QlikView will join on multiple fields it is best to avoid this, so if you do have a join that requires multiple fields then you should create a key specifically for the QlikView table by adding fields together in both table to create a single field.

As I said above, join the QlikCommunity (www.qlikcommunity.com) and you'll find a much better service for your questions.

---

There is a way to prevent QlikView from joining fields with similar names automatically.

Say, if you two tables Tab1 and Tab 2 with similar field names, however different in their own context, you could use the "noconcatenate" keyword to prevent QlikView from automatically making an association.

---

you can edit the load script in QlikView (CTRL+E) and use something like this:

Work:
LOAD
   EmployeeId,
   EmployeePerson,
   DepatmentId,
   EmployeeFlags,
   DepartmentName,
   DepartmentFlags

SELECT Employee.Id as EmployeeId, Employee.Person as EmployeePerson, Employee.DepartmentID as DepatmentId, Employee.Flags as EmployeeFlags, Department.Name as DepartmentName, Department.Flags as DepartmentFlags
FROM Employee, Department
WHERE Employee.DepartmentID = Department.Id

(Did not try it, but you should get the idea)

---

This should work

    employee:
LOAD * INLINE [
    ID, Person, DepartmentID, Flags
    1000, Bob, 2001, 1000000
    1001, Sue, 2002, 1100000
];

department:
LOAD * INLINE [
    ID, Name, Flags
    2001, HR, 01101111
    2002, R&D, 1100000
];

left join (employee)
LOAD ID AS DepartmentID,
     Name,
     Flags AS Department_Flags
Resident department
;

DROP Table department;

The result shoud look like this:

Kind Regards Daniel