SQL Conversion into Qlikview script

[qlikviewwizard]

Hi All,

Please help in conversion of the below SQL code into Qlikview Script.

SELECT E4.EMPNO,E4.ENAME,E4.SAL+ISNULL(E5.SAL,0)+ISNULL(E8.SAL,0) AS TOTALSAL FROM EMP E4 LEFT OUTER JOIN (SELECT MGRNO,MGRNAME,SAL FROM

(SELECT M.EMPNO AS MGRNO,M.ENAME AS MGRNAME,E.EMPNO,E.ENAME FROM EMP E LEFT OUTER JOIN EMP M ON E.MGR=M.EMPNO) AS E1 INNER JOIN

(SELECT EMPNO,SAL FROM EMP WHERE LAST_DATE IS NOT NULL AND START_DATE>LAST_DATE) AS E2 ON E1.EMPNO=E2.EMPNO) AS E5

ON E4.EMPNO=E5.MGRNO

LEFT OUTER JOIN (

SELECT E6.MGRNO,E6.MGRNAME,SAL FROM

(SELECT M11.EMPNO AS MGRNO,M11.ENAME AS MGRNAME,E11.EMPNO,E11.ENAME FROM EMP E11 LEFT OUTER JOIN EMP M11 ON E11.MGR=M11.EMPNO) AS E6 INNER JOIN

(SELECT MGRNO,MGRNAME,SAL FROM

(SELECT M.EMPNO AS MGRNO,M.ENAME AS MGRNAME,E.EMPNO,E.ENAME FROM EMP E LEFT OUTER JOIN EMP M ON E.MGR=M.EMPNO) AS E1 INNER JOIN

(SELECT EMPNO,SAL FROM EMP WHERE LAST_DATE IS NOT NULL AND START_DATE>LAST_DATE) AS E2 ON E1.EMPNO=E2.EMPNO) E7

ON E6.EMPNO=E7.MGRNO) AS E8 ON E4.EMPNO=E8.MGRNO

Source data:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	START_DATE	LAST_DATE
7369	SMITH	CLERK	7902	17-Dec-1980	800	NULL	20	15-May-2015	12-May-2015
7499	ALLEN	SALESMAN	7698	20-Feb-1981	1600	300	30	15-May-2015	NULL
7521	WARD	SALESMAN	7698	22-Feb-1981	1250	500	30	15-May-2015	NULL
7566	JONES	MANAGER	7839	2-Apr-1981	2975	NULL	20	15-May-2015	NULL
7654	MARTIN	SALESMAN	7698	28-Sep-1981	1250	1400	30	14-May-2015	16-May-2015
7698	BLAKE	MANAGER	7839	1-May-1981	2850	NULL	30	14-May-2015	NULL
7782	CLARK	MANAGER	7839	9-Jun-1981	2450	NULL	10	14-May-2015	NULL
7788	SCOTT	ANALYST	7566	9-Dec-1982	3000	NULL	20	14-May-2015	NULL
7839	KING	PRESIDENT	NULL	17-Nov-1981	5000	NULL	10	14-May-2015	NULL
7844	TURNER	SALESMAN	7698	8-Sep-1981	1500	0	30	14-May-2015	NULL
7876	ADAMS	CLERK	7788	12-Jan-1983	1100	NULL	20	14-May-2015	NULL
7900	JAMES	CLERK	7698	3-Dec-1981	950	NULL	30	14-May-2015	NULL
7902	FORD	ANALYST	7566	3-Dec-1981	3000	NULL	20	14-May-2015	NULL
7934	MILLER	CLERK	7782	23-Jan-1982	1300	NULL	10	14-May-2015	12-May-2015

Thanks in advance.

[anbu1984]

You can directly run the Sql as is in Qlikview. Why do you want convert Sql into Qlikview script?

[qlikviewwizard]

Dear anbu1984

Actually the data is coming from flat files or QVDs.

Can I use as it is? Please advise.

[anbu1984]

In that case you have to write Qlikview script. Can you explain what are you trying to accomplish? Why do you have same code twice in Left outer join?

[Peter_Cammaert]

It would certainly be more efficient to translate your intention into a LOAD SCRIPT. Can you describe what should happen to the source data when you load it into QlikView?

Peter

[qlikviewwizard]

Hi anbu1984 and pcammaert,

If START_DATE>LAST_DATE then EMP SAL should allot to his/her MGR (Managers)

For example SMITH manager is FORD (7902) and his manager is JONES (7566) . So SAL should add to these two managers as START_DATE>LAST_DATE for SMITH.

Even for MILLER also need to send his SAL (1300) to his managers CLARK and his manager KING.

Please advise.

[anbu1984]

Check this app

[engishfaque]

Dear Wizard,

This is great question, I'm also looking for answer for that kind of question means SQL Query to QlikView Script conversion.

Kind regards,

Ishfaque Ahmed

[jagan · Accepted solution]

Hi,

Try Self join

Self join

Regards,

Jagan.