topic Reference key problem in QlikView

Reference key problem

Thu, 14 Apr 2011 20:06:55 GMT

Hi All,

guys.. what's the better way to resolve this problem:

In this model, the table TabA join TabB only using the field tabBID. And each table be join with TabC using tabCID each one.

To solve this, I'm thinking to create other tabC, with

other key (tabCID2). And use this new key to join with one of each other tables. Am I on right way?

tks

Reference key problem

Miguel_Angel_Baeyens — Thu, 14 Apr 2011 20:23:39 GMT

Hello Rodrigo,

If you want to avoid the synthetic key, you can create composite keys, for example

TabA:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabAID, Afield1FROM tableA; TabB:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabBID, Bfield1FROM tableB; TabC:LOAD tabCID, Cfield1, Cfield2, Cfield3FROM tableC;FROM table;

Anyway, a synthetic key is not an error by itself, it only means that you have more than one table with more than one field named alike.

Hope that helps.

Reference key problem

Oleg_Troyansky — Thu, 14 Apr 2011 20:23:41 GMT

This structure needs to be simplified. The specific way would depend a lot on your business needs and on the relations between those tables.

As an example, if Table A and Table B can be combined into a single table (don't worry about de-normalization, we do it successfully every day), then you are only left with two tables joined by a single key, which is fine.

In a different situation, the right solution would be concatenating all three tables into a single fact. Depending on your analysis needs, this may or may not be the right solution...

Reference key problem

Thu, 14 Apr 2011 20:52:49 GMT

Hello, Miguel!

I understood your diea, but the problem is that tables TabB and TabC are linked only using tabBID, and not a composite key like tabBID+tabCID.

Oleg,

If I use "join" and "concatenate" commands, would I lost data? Or can I solve this using LEFT, RIGHT and FULL joins?

tks

Reference key problem

Fri, 15 Apr 2011 03:19:05 GMT

Rodrigo;

I came across your thread looking for composite key help, but I can answer your question to Oleg -

You won't lose data. Without knowledge of the contents of your tables I can't gauruntee it but we used to have a similar problem to what you are having and solved it by concatenating two tables and left joining another. Works a treat and having all the data in one table improved performance.

Reference key problem

Oleg_Troyansky — Fri, 15 Apr 2011 14:38:12 GMT

You will never lose data using COncatenate (it's appending one table to another, similar to SQL term "UNION ALL", only allows different table structure).

With LEFT, RIGHT or INNER join, you may lose some data that doesn't exist in the the "primary" table (the type of the join determines what table is "Primary"). By default, JOIN is outer, so you won't lose any data if you use the default.