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

[#2]

Hi Peter,

Thanks a lot QL and Peter ..It was Exists and now It is working fine as expected ..

But can you explain me how the flow is going on ...means actually what happens in code...little bit i did not understand ...

Regards

Sarfaraz

[peterwh]

Hi,

I will try to explain. Your statement has to be splitted in two different loads.

1.

Dependent:

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

This is the normal way to load data from a SQL-Database. The load before the SQL-statement is a so called "preceding load" (preceding load is often used and is explained in the documentation or community). In this context it is used, because you can use all Qlikview-functions in such a load, so you have one step to load from the database and use QV-functions (i.e hour(Timestamp) As Hour).

2.

NOCONCATENATE LOAD *

WHERE NOT EXIST(MGRSSN)

;

LOAD

MGRSSN,

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

If you look closely you can see, that it is nearly the same as above (SQL-statement with a preceding load). Only there is a preceding load before the preceding load "Noconcatenate Load....". So I will only try to explain this second preceding load.

"Noconcatenate" means, that Qlikview will make a new table, even there's a table with the same field names.

Now all data gets loaded where MGRSSN-data (from the "Dependent"-table, so "E_SSN") doesn't match MGRSSN-data from the "Department" table. So your SQL-statement was transformed in a QV-statement.

I think you could simplyfy the second part by (but its not tested):

NoConcatenate LOAD

MGRSSN,

"D_NAME",
"D_NO" AS D_NUM,
MGRSSN ,
"MGR_START_DATE"

WHERE NOT EXIST(MGRSSN);
SQL SELECT *
FROM SYSTEM.DEPARTMENT ;

I hope my short explanation will point you in the right direction to understand the script Q L made.

Kind regards

Peter

[jvitantonio]

Hello Sarfaraz,

Let's see  if I can make it clearer.

Before using the function Exists() you need to previously load a field you want to use within this functions. You main goal is to load departments which are not in Dependent.

To achieve this, you first load the list of dependent and then you load your departments using but filtering the records that are not in the dependent table (where not exitst()).

Now, if you take a closer look at the Department load, we have what we call a Precedent Load. In your case, you are loading data from SQL and you won't be able to use exitst() in this case. This is why we use the precedent load right above the SQL load. Logically, Qlikview will first execute the lowest query (then one with SQL) and the up to the final precedent Load. This is what ti will do:

1) Load Dependent Table

2) Load Department Records from SQL

3) Filter Department Records. You'll get those records in Department but NOT in Dependent

4) We delete table Dependent as it's no longer needed

I hope this helps.

JV