topic Re: Table inner join itself in QlikView

Table inner join itself

Fri, 17 Apr 2015 03:11:02 GMT

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:

Key	EE ID	Region
1	1	US
2	1	APAC
3	2	APAC
4	2	US

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

Section Access:

Key	EE ID	Region
1	1	US
1	1	APAC
2	1	APAC
3	2	APAC
3	2	US
4	2	US

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

Anonymous — Fri, 17 Apr 2015 19:58:50 GMT

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;

Re: Table inner join itself

Mon, 20 Apr 2015 18:48:34 GMT

Hello,

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:

Table1:

LOAD * INLINE [

Key,eeid,region

1,1,US

2,1,APAC

3,1,EMEA

4,2,APAC

5,2,US

];

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?

Thanks in advance for your help.

Re: Table inner join itself

Anonymous — Mon, 20 Apr 2015 19:38:56 GMT

try this,

Table1:

LOAD * INLINE [

Key,eeid,region

1,1,US

2,1,APAC

3,1,EMEA

4,2,APAC

5,2,US

];

join (Table1)

Load

eeid,

Key as Key2,

region as region2

resident Table1;

Table2:

NoConcatenate

Load Distinct

Key,

eeid,

region2 as region

resident Table1

where Key2>=Key

;

drop table Table1;

Re: Table inner join itself

Mon, 20 Apr 2015 22:22:21 GMT

This works like a charm! Thank you

Re: Table inner join itself

Anonymous — Mon, 20 Apr 2015 23:09:31 GMT

I'm glad I could help.