5 Replies Latest reply: Mar 10, 2016 12:30 PM by Jay Fitchett RSS

    Loading Definition Tables Multiple Times

    Jay Fitchett

      Hi there,

      I'm pretty new to Qlik and have encountered a situation where a fact table needs to link to a definition three times. My fact (OR_Data) contains columns that hold IDs to link to my definition (Physician). My solution is to load and rename the Physician table three times. Here's my load script for a testing scenario:

       

      LOAD

          Booking_Surgeon_ID,

          Anesthesiologist_ID,

          Operating_Surgeon_ID,

          Procedure

      FROM [lib://AttachedFiles/PhysicianTest.xlsx]

      (ooxml, embedded labels, table is OR_Data);

       

      Booking_Surgeon:

      LOAD

          Physician_ID as Booking_Surgeon_ID,

          Physician_Code as Booking_Surgeon_Code,

          Physician_Name as Booking_Surgeon_Name

      FROM [lib://AttachedFiles/PhysicianTest.xlsx]

      (ooxml, embedded labels, table is Physician);

       

      Anesthesiologist:

      LOAD

          Physician_ID as Anesthesiologist_ID,

          Physician_Code as Anesthesiologist_Code,

          Physician_Name as Anesthesiologist_Name

      FROM [lib://AttachedFiles/PhysicianTest.xlsx]

      (ooxml, embedded labels, table is Physician);

       

      Operating_Surgeon:

      LOAD

          Physician_ID as Operating_Surgeon_ID,

          Physician_Code as Operating_Surgeon_Code,

          Physician_Name as Operating_Surgeon_Name

      FROM [lib://AttachedFiles/PhysicianTest.xlsx]

      (ooxml, embedded labels, table is Physician);

       

      This works fine, but what I'd like is for the three physician tables to only hold entries that exist in their respective columns in the fact table. The physician table has hundreds of entries, but the OR_Data table might only have 4 distinct physician IDs in the Booking_Surgeon_ID column. I'd only want 4 entries in the Booking_Surgeon table. I've read about the WHERE EXISTS keyword, but can't seem to figure out what to use in the exists expression. Any hints?

       

      Also - is this the best approach in this scenario? I have a feeling I'll be coming up against this sort of situation often.

       

      Thanks!!

        • Re: Loading Definition Tables Multiple Times
          Nicole Smith

          Fact:

          LOAD

              Booking_Surgeon_ID,

              Anesthesiologist_ID,

              Operating_Surgeon_ID,

              Procedure

          FROM [lib://AttachedFiles/PhysicianTest.xlsx]

          (ooxml, embedded labels, table is OR_Data);

           

          Booking_Surgeon:

          LOAD

              Physician_ID as Booking_Surgeon_ID,

              Physician_Code as Booking_Surgeon_Code,

              Physician_Name as Booking_Surgeon_Name

          FROM [lib://AttachedFiles/PhysicianTest.xlsx]

          (ooxml, embedded labels, table is Physician)

          WHERE exists(Booking_Surgeon_ID, Physician_ID);

           

          Anesthesiologist:

          LOAD

              Physician_ID as Anesthesiologist_ID,

              Physician_Code as Anesthesiologist_Code,

              Physician_Name as Anesthesiologist_Name

          FROM [lib://AttachedFiles/PhysicianTest.xlsx]

          (ooxml, embedded labels, table is Physician)

          WHERE exists(Anesthesiologist_ID, Physician_ID);

           

          Operating_Surgeon:

          LOAD

              Physician_ID as Operating_Surgeon_ID,

              Physician_Code as Operating_Surgeon_Code,

              Physician_Name as Operating_Surgeon_Name

          FROM [lib://AttachedFiles/PhysicianTest.xlsx]

          (ooxml, embedded labels, table is Physician)

          WHERE exists(Operating_Surgeon_ID, Physician_ID);

          • Re: Loading Definition Tables Multiple Times
            Colin Albert

            You could use mapping tables.

             

            Physician_Code_Map:

            MAPPING LOAD

                Physician_ID,

                Physician_Code

            FROM [lib://AttachedFiles/PhysicianTest.xlsx]

            (ooxml, embedded labels, table is Physician);


            Physician_Name_Map:

            MAPPING LOAD

                Physician_ID,

                Physician_Name

            FROM [lib://AttachedFiles/PhysicianTest.xlsx]

            (ooxml, embedded labels, table is Physician);

             

            Fact:

            LOAD

                 Booking_Surgeon_ID,

                 Anesthesiologist_ID,

                 Operating_Surgeon_ID,

                 applymap('Physician_Code_Map', Booking_Surgeon_ID) as Booking_Surgeon_Code,

                  applymap('Physician_Code_Map', Anesthesiologist_ID) as Anesthesiologist_Code,

                  applymap('Physician_Code_Map', Operating_Surgeon_ID) as Operating_Surgeon_Code,

                  applymap('Physician_Name_Map', Booking_Surgeon_ID) as Booking_Surgeon_Name,

                  applymap('Physician_Name_Map', Anesthesiologist_ID) as Anesthesiologist_Name,

                  applymap('Physician_Name_Map', Operating_Surgeon_ID) as Operating_Surgeon_Name,

                 Procedure

            FROM [lib://AttachedFiles/PhysicianTest.xlsx]

            (ooxml, embedded labels, table is OR_Data);