6 Replies Latest reply: Nov 11, 2010 5:55 PM by John Witherspoon RSS

    Linking Non keyed tables

    andrewgb

      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
          dbekas

          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
              andrewgb

              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
                  andrewgb

                  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
                      dbekas

                      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
                          andrewgb

                          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
                              John Witherspoon

                               


                              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.