Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Table inner join itself

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?

Tags (2)
1 Solution

Accepted Solutions
amarbogam
Contributor

Re: Table inner join itself

try this,

temp.jpg

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;

5 Replies
amarbogam
Contributor

Re: Table inner join itself

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;

Not applicable

Re: Table inner join itself

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:

table.JPG

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.

amarbogam
Contributor

Re: Table inner join itself

try this,

temp.jpg

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;

Not applicable

Re: Table inner join itself

This works like a charm! Thank you

amarbogam
Contributor

Re: Table inner join itself

I'm glad I could help.

Community Browser