Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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;
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;
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!
the inner join is the sql where deptno; or I'm missing something?
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.
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.
Thank you so much Jagan,Massimo Grossi and Oleg Troyansky.
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?