3 Replies Latest reply: Aug 24, 2016 8:01 PM by Marco Wedel RSS

    Associating data between tables

    John Simmons

      I've got a table (Table1) that looks like so:

       

      DEPT, Clinic, MEPRSGroup

       

      where MEPRSGroup is a comma-delimited string.

       

      I've got another table (Table2) that has a structure like so:

       

      MEPRS MyDate Value1 Value2

       

      I want to group Table2 by MyDate, where MEPRS is contained in Table1.MEPRSGroup, and associate the record in table2 with the Table1.ClinicName and Table1.Department. So, Table1 contains the something like the following:

       

      Dept1, Clinic1, "Meprs1,Meprs2,Meprs3"

       

      And Table2 contains something like the following:

       

      Meprs1, 01/01/2016, 1, 2

      Meprs2, 01/01/2016, 5, 10

       

      I want Table2 to have the following:

       

      01/01/2016, 6, 12, Dept1, Clinic1

       

      How would I go about doing that?

        • Re: Associating data between tables
          Liron Baram

          hi

          in your load script of table one

          you can use subfield function

          the script should look like this :

          load DEPT,Clinic, subfield(MEPRSGroup,',') As MEPRS

          from Your source

          this will create a row for each value in the MEPRSGroup field ,

          then by using left join with Table2 you can get the result you want

            • Re: Associating data between tables
              John Simmons

              So, I returned all of the LOAD statements to their most basic content, and I'm STILL getting the "loop detected" warning and I'm STILL getting he object memory error for the table object. I even rebooted my machine trying to solve that.

               

               

              I can't find anything resembling a loop in this code (and before I did what you said to do, I wasn't having this problem) :

               

               

              PC_Clinics:
              LOAD Department, MEPRS4, ClinicName
              FROM PC_Clinic_Table.qvd (qvd)
              WHERE (ClinicName <> 'SRP' AND ClinicName <> 'PEDS SUBS' AND ClinicName <> 'IMC SPECS');

               

              DailyF2F_0:
              LOAD Week_End_Dt AS Week_End_Date
              ,
              ClinicName
              ,
              [Clinic-Weekly Avail Appt Goal] AS StdValue
              FROM [excel file.xlsx]  (ooxml, embedded labels, table is [Clinic Weekly Avail Appt])
              WHERE (ClinicName <> 'SRP' AND ClinicName <> 'PEDS SUBS' AND ClinicName <> 'IMC SPECS');

              TOCW_0:
              LOAD MEPRS4
              ,
              Week_End_Date
              ,
              SUM(IF(ISNULL([24HR_Num]),0,[24HR_Num])) AS [24HR_Num]
              ,
              SUM(IF(ISNULL([24HR_Den]),0,[24HR_Den])) AS [24HR_Den]
              ,
              SUM(IF(ISNULL(FTR_Num),0, FTR_Num)) AS FTR_Num
              ,
              SUM(IF(ISNULL(FTR_Den),0, FTR_Den)) AS FTR_Den
              ,
              SUM(IF(ISNULL(SPEC_Num),0, SPEC_Num)) AS SPEC_Num
              ,
              SUM(IF(ISNULL(SPEC_Den),0, SPEC_Den)) AS SPEC_Den
              ,
              SUM(IF(ISNULL(WELL_Num),0, WELL_Num)) AS WELL_Num
              ,
              SUM(IF(ISNULL(WELL_Den),0, WELL_Den)) AS WELL_Den
              FROM TOC_3rdNext_W.qvd (qvd)
              WHERE MEPRS4 <> 'BDAA' and MEPRS4 <> 'BAAA'
              GROUP BY MEPRS4, Week_End_Date;

            • Re: Associating data between tables
              Marco Wedel

              Hi,

               

              one solution could be:

               

              QlikCommunity_Thread_230136_Pic1.JPG

               

               

              QlikCommunity_Thread_230136_Pic5.JPG

              QlikCommunity_Thread_230136_Pic6.JPG

              QlikCommunity_Thread_230136_Pic2.JPG

              QlikCommunity_Thread_230136_Pic3.JPG

              QlikCommunity_Thread_230136_Pic4.JPG

               

               

              Table1:
              LOAD * INLINE [
                  DEPT, Clinic, MEPRSGroup
                  Dept1, Clinic1, "Meprs1,Meprs2,Meprs3"
              ];
              
              Table2:
              LOAD * INLINE [
                  MEPRS, MyDate, Value1, Value2
                  Meprs1, 01/01/2016, 1, 2
                  Meprs2, 01/01/2016, 5, 10
              ];
              
              tabLink:
              LOAD Distinct
                   MEPRSGroup,
                   SubField(MEPRSGroup,',') as MEPRS
              Resident Table1;
              
              

               

               

              hope this helps

               

              regards

               

              Marco