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:
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
'$(key)' as Key,
Region as REGIONTEST
where Employee = $(eeid) AND Key >= $(key); //ORDER BY Employee, Key;
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:
ID =IDENTITY(INT, 1, 1),
INNERJOIN #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?
Thanks for the reply. This works if each employee has only 2 transactions. however if the employee has more than 2 it does not flag it correctly, for example if we change the load to:
LOAD * INLINE [
I get this:
For employee 1, keys 2 and 3 are fine since they produce the regions with higher key number, however Key 1 does not contain the key between 1 and 3 which is APAC. I believe this is due to the Mapping function using Max and Min.
Is there anyway to do a mapping to multiple values?