Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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);
Hi,
Try like this,
Dependent:
Load
* from Dependent;
Department:
Load * from
Department where not exists(Dependent.E_SSN,MGRSSN);
Regards
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.
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
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
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);
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;
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)
Hi,
you have a typo in it. Its "exists" not "exist".
Peter