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:
I am trying to join it to itself to obtain the following:
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?