Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (1)
9 Replies
Not applicable

Re: Synthetic tables

can u post ur fields/data model ?

khadeer_sparks
Valued Contributor

Re: Synthetic tables

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

Re: Synthetic tables

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_sparks
Valued Contributor

Re: Synthetic tables

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

Re: Synthetic tables

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

Re: Synthetic tables

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_sparks
Valued Contributor

Re: Synthetic tables

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

Re: Synthetic tables

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_sparks
Valued Contributor

Re: Synthetic tables

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.