topic Re: Query....Please help in QlikView

Query....Please help

Wed, 03 Sep 2014 09:58:10 GMT

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

Re: Query....Please help

MK_QSL — Wed, 03 Sep 2014 10:01:08 GMT

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);

Re: Query....Please help

PrashantSangle — Wed, 03 Sep 2014 10:02:08 GMT

Hi,

Try like this,

Dependent:

Load

* from Dependent;

Department:

Load * from

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

Regards

Re: Query....Please help

MayilVahanan — Wed, 03 Sep 2014 10:06:06 GMT

Try with Not Exists() function

Load *;

Select * from Dependent;

Load * where not exists(E_SSN);

Select * from department;

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

Re: Query....Please help

VishalWaghole — Wed, 03 Sep 2014 10:58:53 GMT

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

Re: Query....Please help

jvitantonio — Wed, 03 Sep 2014 11:14:15 GMT

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.

Re: Query....Please help

Wed, 03 Sep 2014 11:17:23 GMT

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);

Re: Re: Query....Please help

jvitantonio — Wed, 03 Sep 2014 13:43:37 GMT

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;

Re: Query....Please help

Thu, 04 Sep 2014 07:33:51 GMT

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)

Re: Query....Please help

peterwh — Thu, 04 Sep 2014 07:37:15 GMT

Hi,

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

Peter

Re: Query....Please help

Thu, 04 Sep 2014 07:55:48 GMT

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

Re: Query....Please help

peterwh — Thu, 04 Sep 2014 08:23:45 GMT

Hi,

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

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).

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

Re: Query....Please help

jvitantonio — Thu, 04 Sep 2014 09:01:31 GMT

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.