topic Linking Non keyed tables in QlikView

Linking Non keyed tables

Thu, 11 Nov 2010 19:11:29 GMT

I have 4 tables with a Claim_id and one table that has duplicates and such of claim_ids from all of those tables.

Those fields also have differn't names

How can I load a few tables then say link field to field?

like:

link table1.CLAIM_ID to table2.SCLAIM_ID

Its a basic question but I haven't a good answer other than do a join which I can't do with the table structure

thanks for the help

Linking Non keyed tables

Thu, 11 Nov 2010 19:16:10 GMT

In one of the tables, you can load the field a second time using the same name as in the other table.

Linking Non keyed tables

Thu, 11 Nov 2010 19:34:58 GMT

Im not sure i understand

Well I apologize if i'm not explaining this well, I'm stuck in a all day meeting. But essentially I have a table that has 2 entries of the claim_id field, due to this ones called Sclaim_id, and ones called Eclaim_id. Those can be populated by a claim_id from one of 4 other tables. So I cant enforce a key. I cant do a join also because i'm working million of rows so i would need to do 8 joins on 5 tables which would be a huge performance hit just to load the tables. So i'm hoping i can load the 5 tables then say point this to this and such.

Linking Non keyed tables

Thu, 11 Nov 2010 19:38:34 GMT

i thought maybe i could define a set of synthetic keys and give those the same name to tie it all together but im not sure if that would work or how to do it

$Syn1: select claim_id from table?

Linking Non keyed tables

Thu, 11 Nov 2010 20:13:25 GMT

So, if I understand...

Table 1 has:
SClaim_ID
EClaim_ID (which happens to be a duplicate of SClaim_ID)

Tables 2 - 4 have
Claim_ID

Am I close?

Linking Non keyed tables

Thu, 11 Nov 2010 20:47:29 GMT

Thats about right I forgot the database name for it but i'm doing a choose from 4 tables for claim ids.

Essentially I have a table that says 1 claim flags another 1, those claim come from 4 tables that have different field sets. They sum well over 300 columns so Cant be put in a single table, but by design and the type of data and queries run on it are de-normalized for performance

so how do you manage two foreign keys in a table to the same primary key?

Linking Non keyed tables

johnw — Thu, 11 Nov 2010 22:55:35 GMT


andrewgb wrote:so how do you manage two foreign keys in a table to the same primary key?

One common option is to duplicate a table, which isn't practical for your case. But to cover what that would look like just so you know, the table with SClaim_ID and EClaim_ID would stay the same, but your other tables would all get SClaim and EClaim versions, keyed by SClaim_ID and Eclaim_ID respectively, and with all of the other field names renamed to match the table they're on, like for instance SClaim_Date or some such instead of Date. That's a huge amount of extra data and extra complexity, and almost certainly not what we want here.

Another option, perhaps a bit less common, is to create a new table just for the references from the first table. So if your current data looks like this:

UniqueKey, SClaim_ID, EClaim_ID, OtherField1, OtherField2
1, Claim1, Claim2, A, B

You would convert that data to two tables, one of which has two rows for the key:

UniqueKey, OtherField1, OtherField2
1, A, B

UniqueKey, ClaimType, Claim_ID
1, SClaim, Claim1
1, EClaim, Claim2

Converting columns into rows is fairly simple in QlikView. And there's very little extra data here, and the main extra data (ClaimType) will be heavily compressed. So this is a much more practical option than the first, but since I don't know what you want to DO with the data, I can't tell you if it's an appropriate solution. All I can say is that it resolves the linkage problems, because now both claim IDs will map to the other claim tables.