topic Re: SQL Conversion into Qlikview script in QlikView

SQL Conversion into Qlikview script

qlikviewwizard — Sat, 16 May 2015 15:17:30 GMT

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.

Re: SQL Conversion into Qlikview script

anbu1984 — Sat, 16 May 2015 15:30:02 GMT

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

Re: SQL Conversion into Qlikview script

qlikviewwizard — Sat, 16 May 2015 15:32:51 GMT

Dear anbu1984

Actually the data is coming from flat files or QVDs.

Can I use as it is? Please advise.

Re: SQL Conversion into Qlikview script

anbu1984 — Sat, 16 May 2015 15:51:31 GMT

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?

Re: SQL Conversion into Qlikview script

Peter_Cammaert — Sat, 16 May 2015 16:27:52 GMT

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

Re: SQL Conversion into Qlikview script

qlikviewwizard — Sun, 17 May 2015 00:19:31 GMT

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.

Re: SQL Conversion into Qlikview script

anbu1984 — Mon, 18 May 2015 15:30:59 GMT

Check this app

Re: SQL Conversion into Qlikview script

engishfaque — Mon, 18 May 2015 20:23:21 GMT

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

Re: SQL Conversion into Qlikview script

jagan — Wed, 20 May 2015 03:46:40 GMT

Hi,

Try Self join

Self join

Regards,

Jagan.