What is the use of Qlikview synthetic join?

[#1]

When adding two new tables into a script Qlikview is automatically creating synthetic tables by comparing column names of the tables (if both column names are same).

Please explain why it is enabled by default in Qlikview?

What is the purpose of this option?

[#2 · Accepted solution]

it is because, when the field names are same it makes the two fields in different table as a join,

if more thn 1 field is same between two table, QV has no way to find out which one is the main link, so what it does is connect all the links, and hence a synthetic key.

if you want to use two fields as link, you will have to join them together and make a third key and make that third as the only join between tables

thr is a use for Synthetic key, but i have never founf out what yet

[#3]

Dear AtifAhmad,

Thanks for your clarification. And I would like to tell you I am very new to Qlikview and just started learning it a week back.

So synthetic key and join are two different things right?

And synthetic key is formed only when more than one column names are same in two different tables?

Is my understanding correct?

People are telling like if synthetic key is formed and a large amount of data needs to be reloaded, the script takes hrs of time to load the data. So you have to avoid synthetic key.

Is that true?

[#4]

welcome to the world of QlikView and happy qliking.

Joins are forced joins like in SQL, Links are automatic links

your understanding upto now is perfect.

yes if the script has synthetic key, it takes it very long to load cause it has to make all the possible combinations and load all the data.

just post your msg on the forum and i am sure some one will help you to learn a good understanding of QlikView.

[#5]

Can I ask you a question on the join?

I have 2 tables with 1 common filed called NTID. Table # 2 has NTID of user and login time. Tables # 1 has NTID and manager id.

Tables # 2 comes from application login tracker and Table # 1 comes from current employee database.

As users join company and leave, Table # 1 has ONLY the current employee records. Tables # 2 as is maintained by the application has history of all the users that are using application and have used application in the past.

I am trying to get this:

Table # 1 Tables # 2

Manager id NTID NTID Login Date

X 1/1/2006

X 1/2/2006

Y 1/2/2006

Z 1/3/2006

P A A 1/1/2010

P B B 1/1/2010

Q C C 1/1/2010

Q D D 1/1/2010

Q E

In this example A,B,C,D and E are active employee where as P and Q are manager of A & B and C, D & E respectively. A, B, C & D are user of the application that is why there is login information for A, B, C & D. E is the odd one out. X, Y & Z are ex-employee who used application in past are not in people database coz they have left the company.

I want to make a bar char with 'Manager id' as dimension and count of distinct (NTID) as expression.

Expected values are:

P = 2

Q = 2 but I get Q = 3.

How do I eliminate the last row? I am loading both the tables # 1 & # 2 as is from the Oracle databases and letting QV do the join.

Neaz

[johnw]

I'm guessing, but perhaps this:

count(distinct if("Login Date","NTID"))

[#7]

Thanks John, it worked. Getting to know QV better now.