Query....Please help

[#1]

Dear All,

Can anyone please tell me how to build the logic in QlikView for below query.

select MGRSSN from department where MGRSSN not in (select Dependent.E_SSN from Dependent);

suppose there is 2 table one contains a , b, c  and 2nd contains c, d ,    data......and i want d in my result ....how i can do this. 

Regards

Sarfaraz Sheikh

+91 7620140822

[MK_QSL]

Load MGRSSN From Table1:

Load MGRSSN From Table2 Where Not Exists(MGRSSN);

EDIT :

Try Below example to get basic idea.

Load * Inline

[

  Data, Value

  A, 1

  B, 2

  C, 3

];

Load * Inline

[

  Data, Value

  C, 4

  D, 5

] Where Not Exists (Data);

[PrashantSangle]

Hi,

Try like this,

Dependent:

Load

* from Dependent;

Department:

Load * from

Department where not exists(Dependent.E_SSN,MGRSSN);

Regards

[MayilVahanan]

Hi

Try with Not Exists() function

Load *;

Select *  from Dependent;

Load * where not exists(E_SSN);

Select * from department;

or

you can able to use sp, views and select statement directly in qlikview.

[VishalWaghole]

Hi,

You can try it by using Not exists is best possible answer.

Table:

Load

* from Dependent;

Table:

Load * from

Department where not exists(Table.E_SSN,MGRSSN);

-- Regards,

Vishal Waghole

[jvitantonio]

Hi,

You will need to load 2 different tables.

The first table will have the comparison set of data.

The second table will have the remaining data.

Using Not Exists would be the right and most performing approach. However, you need to remember to remove the first loaded table to achieve your final result.

tempTable:

LOAD

    MGRSSN

FROM Dependent.qvd

;

FinalTable:

NOCONCATENATE LOAD

    MGRSSN

FROM Department.qvd

WHERE NOT EXIST(MGRSSN);

DROP TABLE tempTable;

--------------

You can also use your SQL query like this:

LOAD *;

SQL

select MGRSSN from department where MGRSSN not in (select Dependent.E_SSN from Dependent)

;

I hope this helps.

JV

[#7]

Hi all,

Thanks for your reply guyss ......but the problem is not rectify at all ......It is not giving the expected output ....please check my below script code:-

Dependent:

LOAD "E_SSN" ,
"DEPENDENT_NAME",
SEX,
"BIRTH_DATE",
RELATIONSHIP ;
SQL SELECT *
FROM SYSTEM.DEPENDENT;


Department:
LOAD "D_NAME",
"D_NO" AS D_NUM,
MGRSSN ,
"MGR_START_DATE"
where not Exists(Dependent.E_SSN,MGRSSN);
SQL SELECT *
FROM SYSTEM.DEPARTMENT ;//where MGRSSN not in (select E_SSN from DEPENDENT);

[jvitantonio · Accepted solution]

Hello Sarfaraz,

You need to change a couple of things. Here's the code:

Dependent:

LOAD "E_SSN"  AS MGRSSN,
"DEPENDENT_NAME",
SEX,
"BIRTH_DATE",
RELATIONSHIP ;
SQL SELECT *
FROM SYSTEM.DEPENDENT;


Department:

NOCONCATENATE LOAD *

WHERE NOT EXIST(MGRSSN)

;

LOAD

MGRSSN,

"D_NAME",
"D_NO" AS D_NUM,
MGRSSN ,
"MGR_START_DATE"
SQL SELECT *
FROM SYSTEM.DEPARTMENT ;

DROP TABLE Dependent;

[#9]

Hi,

Thanks you for your reply .............Not working with this one also .........It is showing below error..

Error in expression:
EXIST is not a valid function

Department:

NOCONCATENATE LOAD *

WHERE NOT EXIST(MGRSSN)

[peterwh]

Hi,

you have a typo in it. Its "exists" not "exist".

Peter