topic Re: Subquery in Qlikview script in QlikView

Subquery in Qlikview script

Fri, 13 Feb 2015 17:37:41 GMT

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?

Re: Subquery in Qlikview script

Gysbert_Wassenaar — Fri, 13 Feb 2015 18:59:20 GMT

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;

Re: Subquery in Qlikview script

maxgro — Fri, 13 Feb 2015 20:48:02 GMT

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;

Re: Subquery in Qlikview script

Oleg_Troyansky — Fri, 13 Feb 2015 20:52:25 GMT

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!

Re: Subquery in Qlikview script

maxgro — Fri, 13 Feb 2015 21:09:04 GMT

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

Re: Subquery in Qlikview script

Oleg_Troyansky — Fri, 13 Feb 2015 22:11:33 GMT

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.

Re: Subquery in Qlikview script

jagan — Mon, 16 Feb 2015 07:10:22 GMT

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.

Re: Subquery in Qlikview script

Mon, 16 Feb 2015 11:03:19 GMT

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

Re: Subquery in Qlikview script

somacdc — Tue, 06 Aug 2019 10:41:10 GMT

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?