Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Synthetic tables

Hello everyone,

I am joining tables from two different databases (one in SQL, one in Jade)

I'm trying to design a summary of transaction by employee code (common in both dbs), and transaction type (I've created a conversion table for that), and by date grouped into months.

This alllowes me to compare transactions in both db at the same time, so that if I change criteria on a list box, it actually filters data in both dbases.

My summary seems to work but if I check the links, it looks like QlikView has created Synthetic keys, which I believe should be avoided at all cost (Im new to QV).

Can I avoid this problem, still allowing a user to only enter criteria once?

Thanks for your time

9 Replies
Not applicable
Author

can u post ur fields/data model ?

khadeer
Specialist
Specialist

u have to give assocaition r joins... morethan one field having same name in two r more tables synthetic key will form, so u have to avoid this by changing the names expect one field, which is key in both tables. Post ur sample data.

Not applicable
Author

Structure.bmp

Hello, does this help or you need me to be more specific? Each of the two tables is a series of joined table (all inner)?

khadeer
Specialist
Specialist

I think unicode or employee code is common in both tables, so take only one field, for remaining both change their names using alias or qualify function. It will resolve ur prblm

Not applicable
Author

u need to identify which of the 3 fields involved in the syn key can uniquely identify each row in both the tables.

it could be only 1 field of the 3 fields, cud be 2 fields or all 3.fields

case 1) only 1 key field then rename other 2

case 2) make composite key for eg if EmpCode & unicode identify each row uniquely then,EmployeeCode &'-'& UniCode as EmpUniKey and rename the individual fields

case 3) all 3 fields EmployeeCode &'-'& UniCode &'-'& MonthD as EmpUniMonKey and rename these individual fields

Not applicable
Author

Probably a silly question, but, I gave the same name so that my user doesn't need to specify the criteria more than once, eg. if he/she chooses one employe, in a specific month, a certain transaction type, I need my data to all change accordingly so that the user can easily compare similar data but coming from two databases.

Does that make sense? Or maybe I should join the main two tables in the script, but then ... can I do that if the tables need an ODBC connection to two different servers?

khadeer
Specialist
Specialist

send ur sample data with 10 r 15 lines with both tables, if u have both tables with all same fields then concatanate the both tables.

Not applicable
Author

Ok, I think I'm in trouble... see below my other answer please?

Thanks for that, I think I need to reconsider my entire structure... and understand associations better.

khadeer
Specialist
Specialist

If u r having unique employee code in both tables, i mean for every employee (he/she) there is no need to join just give the association with employee id. Just do like this, change ur field names expect employee id, dont make join, load both tables ... it will give u result what u r expecting.