topic Re: How to use sub-queries in qlikview scipting in QlikView

How to use sub-queries in qlikview scipting

Sun, 28 Dec 2014 11:11:27 GMT

How to use sub-queries in qlikview scripting?

Please explain, thanks in advance.

Re: How to use sub-queries in qlikview scipting

Gysbert_Wassenaar — Sun, 28 Dec 2014 11:47:07 GMT

Qlikview does not know sub-queries. If you create a select statement in the Qlikview script (or any sql statement preceded by the SQL keyword) then that statement is not processed by Qlikview, but send to the database server for execution. That means you can (in fact must) use the SQL dialect your database server understands. If your database server can handle sub-queries then you can use them in the sql statement.

Re: How to use sub-queries in qlikview scipting

anbu1984 — Sun, 28 Dec 2014 12:59:54 GMT

Preceding Load

Load Fld1,Max(Fld2) Group by Fld1; //Preceding load

Load * Inline [

Fld1,Fld2

1,100

1,200

2,50

2,150 ];

Re: How to use sub-queries in qlikview scipting

its_anandrjs — Sun, 28 Dec 2014 13:03:54 GMT

Hi,

You can not use Sub Queries into qlikview like which is SQL, but you can use resident table or any other table load and then use Joins ( inner, left , right ) and make single table out of that tables bu conditions.

Regards

Anand

Re: How to use sub-queries in qlikview scipting

Sun, 28 Dec 2014 16:19:06 GMT

Use QlikView functions in order to achieve it. For instance, having the following SQL Statement in a traditional applcation

SELECT department_name, employee_name, count(*) as numberOfEmployees FROM EMPLOYEE where EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID_DEPARTMENT AND DEPARTMENT.ID_DEPARTMENT IN (SELECT ID_DEPARTMENT FROM REGION WHERE ID_REGION="NORTH") group by department_name;

In QlikView , forget abut the latter statetement 🙂 , it is very easy, since QView in the majority of its objects aggregates automatically. For instance, the result in a straight chart table would be:

1) Add the following as Dimensions

department_name, employee_name

and then

2) Add the following Expression and label it as numberOfEmployees

IF (ID_REGION = 'NORTH',COUNT(employee_name),0)

IF (MATCH(ID_REGION , 'NORTH'),COUNT(employee_name),0)