6 Replies Latest reply: May 17, 2012 8:52 AM by l33h0885 RSS

    Joining 4 tables that share 3 fields

      Hi All,

       

      Not sure of the best way to describe this - I am trying to join 3 tables to a 4th using a field 'USER_ID'.  I want to be able to filter the data based on USER_ID or DATE and bring through ACC_NO, along with sums/counts of various data in each table.

       

      If I just bring through the tables I get the the attached (Capture.JPG) which created a synthetic key as I expect.  I've read about, but dont fully understand, loosely coupled tables and the Qualify function - can these be used to solve my problem?

       

      Attachment required.jpg shows how I'd liekt he tables to be, but is obviously impossible if I want DATE to be the same field (i.e. I select 01/01/2011 and it counts all TRANS_ID, PLAN_ID and EVENT_ID for each USER_ID relating to that date)

       

      Thanks for any input you guys might have.

        • Joining 4 tables that share 3 fields
          Perumal Ayyappan

          Hi

           

          Use This Script

           

          qualify *;

          Unqualify USER_ID;

           

          User:

          Load USER_ID,

          Name

          From usertable;

           

           

          Trans:

          Load USER_ID,

          Date,

          ACC_NO,

          TRANS_ID

          From transtable;

           

           

          Plan:

          Load USER_ID,

          Date,

          ACC_NO,

          PLAN_ID

          From plantable;

           

          Event:

          Load USER_ID,

          Date,

          ACC_NO,

          EVENT_ID

          From eventtable;

           

           

          Regards,

          Perumal A

          • Re: Joining 4 tables that share 3 fields
            Shivarama krishna K

            Hi ,

             

            Create the Link table using the Commonn fields & create three keys from this table  i.e each key to diff table.

             

            Sample file attached but there is no data ...if the script is not clear to you  let me know..

             

            Regards,

            Shivaram

              • Joining 4 tables that share 3 fields

                Hi Guys,

                 

                Thanks for the input, both ideas very helpful.

                 

                Perumal - your idea worked a treat for linking the tables without causing a loop, however if I try to filter by date there are now 3 date fields instead of 1.

                 

                Shivaram - this idea seems to be closer to the money, but am having a little trouble following the INLINE part.  I have dabbled with inline for mapping loads, but not as you have used them, can you please elaborate on this a little?

                 

                Thanks

                 

                Lee

              • Re: Joining 4 tables that share 3 fields
                Jason Michaelides

                This is a very common requirement!  You have 3 fact tables and one dimension table here.  You need to end up with a single fact table and your dimension table.  Try this script:

                 

                Fact:

                LOAD

                     'Trans'              AS     FactType,

                     RowNo()     AS     FactID,

                     *

                FROM Trans...;

                 

                CONCATENATE (Fact)

                 

                LOAD

                     'Plan'     AS     FactType,

                     RowNo()     AS     FactID,

                     *

                FROM Plan...;

                 

                CONCATENATE (Fact)

                 

                LOAD

                     'Event'     AS     FactType,

                     RowNo()     AS     FactID,

                     *

                FROM Event...;

                 

                Users:

                LOAD

                     *

                FROM Users...;

                 

                That should do it.  If you want to count the different fact types you can use some basic set analysis e.g. COUNT({<FactType='Trans'>} UserID)

                 

                Hope this helps,

                 

                Jason

                  • Joining 4 tables that share 3 fields

                    Qlikview Boy! Thanks you very much - I thought this is what I was doing but didnt seem to work, I have replaced with your formula (obviously tweaked to suit the actual DB tables) and it works a dream.

                     

                    Perumal - I tried your 2nd suggestion and found it works great for the TRANStable, but only pulled through  data from PLAN and EVENT where there was a matching USER_ID, Date and ACC_NO, thus restricting the data further than it should have.

                     

                    Though my problem is now solved, I would like to see the results of Shivaram's suggestion, as the link table idea may prove useful in future.

                     

                    Thanks all!

                  • Joining 4 tables that share 3 fields
                    Perumal Ayyappan

                    Hi

                     

                    Use This Script

                     

                     

                     

                    User:

                    Load USER_ID,

                    Name

                    From usertable;

                     

                    Left join(User)

                    Load USER_ID,

                    Date,

                    ACC_NO,

                    TRANS_ID

                    From transtable;

                     

                     

                     

                    Left join(User)

                     

                     

                    Load USER_ID,

                    Date,

                    ACC_NO,

                    PLAN_ID

                    From plantable;

                     

                    Left join(User)

                    Load USER_ID,

                    Date,

                    ACC_NO,

                    EVENT_ID

                    From eventtable;

                     

                     

                     

                    Regards,

                    Perumal A