5 Replies Latest reply: Apr 20, 2015 7:09 PM by Amar Bogam RSS

    Table inner join itself

    Enrique Teran

      Hello Everyone. Trying to join a table to itself to obtain a result for section access.

       

      The table I currently have in memory in the app looks like this:

       

      Transactions:

      KeyEE IDRegion
      11US
      21APAC
      32APAC
      42US

       

      I am trying to join it to itself to obtain the following:

       

      Section Access:

      KeyEE IDRegion
      11US
      11APAC
      21APAC
      32APAC
      32US
      42US

       

      The objective of this is to flag previous transactions with all the newer regions an employee has moved to. If a user with access only to a newer region for an employee opens the app, the user should be able to access the employee's historic details for that employee but if a user with access to an employees older region only, he should NOT be able to access the details of the employees newer region. Think of HR person accessing details of employees in a region and the employee moved to another region and became CEO, that user should not be able to see that data unless that employee became CEO in a region that the user in the application has access to.

       

      I've tried with a loop in QV, this is what I came up with:

       

      EmpTemp:
      LOAD *,
      RecNo() as Key
      Resident [Employee History] ORDER BY Employee, [Effective_Date];
      
      
      DROP TABLE [Employee History];
      
      
      LET rowText = NoOfRows('EmpTemp'); // get the total number of rows in Timeline table
         
      for i=0 to $(rowText)-1 // loop through every row
                let eeid = Peek('Employee',$(i),'EmpTemp'); //get the value for "EEID" field on each row
                let key = Peek('Key',$(i),'EmpTemp'); //get the value for "Key" field on each row
      
      SecAccessTest:
      LOAD
      '$(key)' as Key,
      Region as REGIONTEST
      Resident [EmpTemp]
      where Employee = $(eeid) AND Key >= $(key); //ORDER BY Employee, Key;
      
      
      
      
      NEXT
      

       

      This gives the desired result but takes over 3 hours for 80,000 records to process due to the loop. A solution in SQL takes a couple of seconds:

       

      SELECT 
             ID = IDENTITY(INT, 1, 1),
             [Employee_ID],
             [Region]
      INTO #SectionAccess
        FROM [GlobalHRDashboard].[dbo].[WorkerInfo]
      ORDER BY
             2,Effective_Date,3
      
      
      
      DROp TABLE #Test 
      
      SELECT DISTINCT
             SA1.ID,
             SA1.Employee_ID,
             SA2.Region
      INTO #Test    
      FROM 
             #SectionAccess SA1
             INNER JOIN #SectionAccess SA2
       ON SA1.ID <= SA2.ID
       AND SA1.Employee_ID = SA2.Employee_ID
      

       

      Is there anyway to achieve this join of the table to itself in QV or do I have to do this in SQL and load SQL table in QV for my section access?

        • Re: Table inner join itself
          Amar Bogam

          Like below? Check the attached file as well.

           

           

          Table1:

          LOAD * INLINE [

              Key,eeid,region

              1,1,US

              2,1,APAC

              4,2,APAC

              5,2,US

          ];

           

           

          Max_Key:

          LOAD

          max(Key) as Max_Key,

          min(Key) as Min_Key,

          eeid as eeid1

          Resident  Table1

          group by eeid

          ;

           

          Map_latestrec:

          mapping

          load

          eeid,

          region

          Resident  Table1

          where Exists(Max_Key,Key)

          ;

           

           

          Concatenate (Table1)

          LOAD

          Min_Key+IterNo()-1 as Key,

          eeid1 as eeid,

          applymap('Map_latestrec',eeid1) as region

          Resident Max_Key

          while IterNo()<=Max_Key-Min_Key

          ;

           

          drop table Max_Key;