Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: Subquery in Qlikview script

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 Replies
MVP & Luminary
MVP & Luminary

Re: Subquery in Qlikview script

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

Re: Subquery in Qlikview script

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;

MVP & Luminary
MVP & Luminary

Re: Subquery in Qlikview script

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!

MVP
MVP

Re: Subquery in Qlikview script

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

MVP & Luminary
MVP & Luminary

Re: Subquery in Qlikview script

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.

MVP & Luminary
MVP & Luminary

Re: Subquery in Qlikview script

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

Re: Subquery in Qlikview script

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

somacdc
New Contributor III

Re: Subquery in Qlikview script

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?