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

Question

Hi!

I'm new to Qlikview and looking for some answers regarding scripting. How can I create Qlickview 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         ,        1000000
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 advice

Kind regards, Karl

Answer 1

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.