Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Subquery in Qlikview script

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?

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

8 Replies
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;


talk is cheap, supply exceeds demand
maxgro
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
MVP
MVP

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

somacdc
Contributor III
Contributor III

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?