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.
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?
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:
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.