topic Re: SQL Between join in script in QlikView

SQL Between join in script

Fri, 13 Feb 2015 16:20:53 GMT

I want to implement the below query in Qlikview.

SQL:

select s.grade ,e.ename,e.sal from emp e,salgrade s where e.deptno in (10,20,30) and 
 hiredate < ('31-DEC-82') and (e.sal between s.losal and s.hisal and s.grade not in 
 (4));

How to update the below script?

EMP:
 LOAD EMPNO, 
 ENAME, 
 JOB, 
 MGR, 
 HIREDATE, 
 SAL, 
 COMM, 
 DEPTNO
 FROM
 EMP.qvd(qvd);
 
 Inner join
 DEPT:
 LOAD DEPTNO, 
 DNAME, 
 LOC
 FROM
 DEPT.qvd(qvd);
 
 SALGRADE:
 LOAD GRADE, 
 LOSAL, 
 HISAL
 FROM
 SALGRADE.qvd(qvd);

Re: SQL Between join in script

Peter_Cammaert — Fri, 13 Feb 2015 16:31:05 GMT

Like this?

EMP:

LOAD ENAME, SAL FROM EMP.QVD (qvd)

WHERE Match(DEPTNO, 10, 20, 30) AND (date#(HIREDATE, 'DD-MMM-YY') < '31/12/1982');

LEFT JOIN (EMP)

INTERVALMATCH (SAL)

LOAD LOSAL, HISAL FROM SALGRADE.QVD (QVD)

WHERE GRADE <> 4;

Re: SQL Between join in script

Fri, 13 Feb 2015 17:24:48 GMT

Thank you Peter,

I want to see the data as shown below:

grade ename sal
1 SMITH 800.00
1 JAMES 950.00
2 WARD 1250.00
2 MARTIN 1250.00
2 MILLER 1300.00
3 ALLEN 1600.00
3 TURNER 1500.00
5 KING 5000.00

Please advise.

Re: SQL Between join in script

Peter_Cammaert — Sun, 15 Feb 2015 19:53:05 GMT

Add another LEFT JOIN at the end:

LEFT JOIN (EMP)

LOAD LOSAL, HISAL, GRADE

FROM SALGRADE.QVD (QVD);

Re: SQL Between join in script

jagan — Mon, 16 Feb 2015 03:16:46 GMT

Hi,

Try this script

Emp:

LOAD

ename,

sal

from emp

where Match(deptno, 10, 20, 30) AND Date(Date#(hiredate, 'DD-MMM-YY')) < MakeDate(1982, 12, 31);

LEFT JOIN(Emp)

IntervalMatch(sal)

LOAD

losal,

hisal,

grade

FROM salgrade

WHERE Not Match(grade, 4);

Hope it helps you.

Regards,

Jagan.

Re: SQL Between join in script

Peter_Cammaert — Mon, 16 Feb 2015 08:05:23 GMT

Did you ever try that code Jagan? I'm convinced that it should work like that too, but it doesn't... There is something weird with INTERVALMATCH and JOIN not doing what they should with additional fields... And IIRC it has been like that for a long time.

Is there any condition that you know of that should be fulfilled before this type of LEFT JOIN INTERVALMATCH will work?