Subquery in Qlikview script

[#1]

I want to show the list the name ,job, dname, location for those who are working as MGRS from EMP and DEPT tables as shown below SQL.

Select e.ename,e.job,d.dname,d.loc,E.SAL from emp e ,dept d

Where e.deptno = d.deptno and e.empno in (select mgr from emp )

Data Output:

ename job dname loc SAL
CLARK MANAGER ACCOUNTING NEW YORK 2450.00
KING PRESIDENT ACCOUNTING NEW YORK 5000.00
JONES MANAGER RESEARCH DALLAS 2975.00
SCOTT ANALYST RESEARCH DALLAS 3000.00
FORD ANALYST RESEARCH DALLAS 3000.00
BLAKE MANAGER SALES CHICAGO 2850.00

How to write the script in Qlikview?

[Gysbert_Wassenaar]

The easiest solution is to simply use the sql statement as is and let the source DBMS execute the statement and return the results.

Other options will first load all the data from the database and the filter those results to the set defined by the mgr values from emp.

T1:

Select mgr from emp;

// Using Left Keep

Left Keep

T2:

Select e.empno mgr, e.ename,e.job,d.dname,d.loc,E.SAL from emp e ,dept d

Where e.deptno = d.deptno;

// Or using Where Exists

T2:

Load * Where Exists(mgr, empno);

Select e.empno, e.ename,e.job,d.dname,d.loc,E.SAL from emp e ,dept d

Where e.deptno = d.deptno;

[maxgro]

manager:

select mgr from emp;

employee:

Load ename, job, SAL, deptno

Where Exists(mgr, empno);

Select * from emp e;

inner join (employee)

load deptno, dname, dloc as loc;

select * from dept d;

drop field deptno;

[Oleg_Troyansky]

Hmmm, I"d stick to the solutions suggested by Gysbert, as less intrusive. The inner join suggested by Massimo ends up affecting the employee table, which may or may not be desirable.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[maxgro]

the inner join is the sql where deptno; or I'm missing something?

[Oleg_Troyansky]

In QlikView, inner join can affect both the source and the target tables. For example, if you have employees that don't exist in the departments table, or the other way, the data will get reduced in both tables.

left keep() is more like the SQL logic - it only affects the target table, but not the source.

[jagan · Accepted solution]

Hi,

Check this script

Emp:

LOAD

     empno,

     ename,

     job,

     SAL,

     deptno

from emp;

INNER JOIN (Emp)

LOAD DISTINCT

     mgr AS empno

FROM emp;

Dept:

LOAD

deptno,

dname,

loc

From dept;

Hope this helps you.

Regards,

Jagan.

[#8]

Thank you so much Jagan,Massimo Grossi and Oleg Troyansky.

[somacdc]

I want to show the list the name ,job, dname, location for those who are not working as MGRS from EMP and DEPT tables as shown below SQL.

Select e.ename,e.job,d.dname,d.loc,E.SAL from emp e ,dept d

Where e.deptno <> d.deptno and e.empno not in (select mgr from emp )

How to write the script in Qlikview?