Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Query....Please help

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

1 Solution

Accepted Solutions
jvitantonio
Specialist III
Specialist III

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;

View solution in original post

12 Replies
MK_QSL
MVP
MVP

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
VishalWaghole
Specialist II
Specialist II

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
Specialist III
Specialist III

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

Not applicable
Author

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
Specialist III
Specialist III

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;

Not applicable
Author

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
Creator II
Creator II

Hi,

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

Peter