Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rulohx87
New Contributor III

Subqueries - script

Hi everybody.

i have a problem with a script. How to do it in qlikview?

SELECT COUNT(*)

FROM   ALUMNOGR A

WHERE  A.CIEGINI=2014 AND A.EAGCVE IN ('P','C')

             AND NOT EXISTS (SELECT     ''

                                        FROM   ALUMNOGR B

                                        WHERE  B.CURP = A.CURP

                                                      AND B.CIEGINI = A.CIEGINI

                                                      AND B.EAGCVE = A.EAGCVE

                                                      AND PESCVE = 2

                                                      AND ALGPROME < 6)

             AND NOT EXISTS (SELECT     ''

                                        FROM   ALUMATER C

                                        WHERE  C.CIEMINICI = A.CIEGINI

                                                      AND C.CURP = A.CURP

                                                      AND C.AMAPROMEDI < 6

                                                      AND A.PESCVE = 2)

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Subqueries - script

Hi,

Try like this

Data:

SELECT

RowNum() AS RecNum,

AutoNumber(CIEGINI & CURP & EAGCVE) AS Key1,

AutoNumber(CIEMINICI & CURP) AS Key2,

*

FROM   ALUMNOGR

WHERE  CIEGINI=2014 ANDEAGCVE IN ('P','C');

Left Join(Data)

SELECT AutoNumber(CIEGINI & CURP & EAGCVE) AS Key1,

1 AS Condition1

FROM   ALUMNOGR

WHERE ALGPROME < 6 AND PESCVE = 2;

Left Join(Data)

SELECT AutoNumber(CIEMINICI & CURP) AS Key2,

1 AS Condition2

FROM   ALUMATER C

WHERE  AMAPROMEDI < 6 AND PESCVE = 2;

Now in Set Analysis use below expression

Count({<Key1-={1}, Key2-={1}>}RecNum) 

Note: The above expression Qlikview will show as syntax error near -=, but it will give the correct result, it is a known error in Qlikview.

Hope this helps you.

Regards,

Jagan.

View solution in original post

10 Replies
Highlighted
nadeemsmarty
Valued Contributor

Re: Subqueries - script

Yoy cannot do this kind of subqurying in qlikview.

You need to create a data model with facts and transactions only.

The aggregation are always performed in the front end .

Regards,

Nadeem

Highlighted
qlikviewwizard
Honored Contributor II

Re: Subqueries - script

Hi Raul,

Bring these tables into datamodel and post the script. We can achieve what ever we want in QV. Thank you.

Highlighted
sasiparupudi1
Honored Contributor III

Re: Subqueries - script

Load the table in qlikview and use set analysis for the counts.

Load primary key,

...,

...,

From your data source

In the front end

Create an expresson for the count

Count ({<CIEGINI={2014},EAGCV={'P','C'},PESCV-={2},AMPAROMEDI-={'<6'}>} primary key)

Hth

Sasi

Highlighted
neetha_p
Honored Contributor

Re: Subqueries - script

Hi Raul,

Please can you post sample data.

Regards

Neetha

Highlighted
MVP & Luminary
MVP & Luminary

Re: Subqueries - script

Have you tried to execute these sql-statement in qlikview? An SQL Select statement won't be directly executed in qlikview it will per odbc/oledb-driver transfered to your source-database which executed the query and returned the results to qlikview. If your odbc/oledb-driver support those statement and you haven't bigger performance issues with this query you don't need to change it.

Of course there are also solutions with qv-scripting possible but for this you will need to load those subqueries separate and use them as join-filter or (combined) field(s) as key in an exists-function.

- Marcus

Highlighted
MVP & Luminary
MVP & Luminary

Re: Subqueries - script

Hi,

Try like this

Data:

SELECT

RowNum() AS RecNum,

AutoNumber(CIEGINI & CURP & EAGCVE) AS Key1,

AutoNumber(CIEMINICI & CURP) AS Key2,

*

FROM   ALUMNOGR

WHERE  CIEGINI=2014 ANDEAGCVE IN ('P','C');

Left Join(Data)

SELECT AutoNumber(CIEGINI & CURP & EAGCVE) AS Key1,

1 AS Condition1

FROM   ALUMNOGR

WHERE ALGPROME < 6 AND PESCVE = 2;

Left Join(Data)

SELECT AutoNumber(CIEMINICI & CURP) AS Key2,

1 AS Condition2

FROM   ALUMATER C

WHERE  AMAPROMEDI < 6 AND PESCVE = 2;

Now in Set Analysis use below expression

Count({<Key1-={1}, Key2-={1}>}RecNum) 

Note: The above expression Qlikview will show as syntax error near -=, but it will give the correct result, it is a known error in Qlikview.

Hope this helps you.

Regards,

Jagan.

View solution in original post

Highlighted
rulohx87
New Contributor III

Re: Subqueries - script

Thank you. jagan looks good idea. I'll try it from loading.

I can not do Set Analysis on the front end. Because there are more than 10 million records in the table ALUMATER for 1 year to 6 years. If I use AGGR, the result take long to appear.



SCRIPT:

ALUMNOGR:

LOAD

     CURP,

     CiEgIni,

     PEsCve,

     EAGCve,

     AlGProme

FROM [$(vPath_EWSCE)ALUMNOGR.QVD] (qvd);

ALUMATER:

LOAD

     CURP,

     CiEMInici,

     PEsCve AS PES_CVE,

     AMaPromedi

FROM

[$(vPath_EWSCE)ALUMATER.QVD](qvd);

UNQUALIFY * ;

Highlighted
qlikviewwizard
Honored Contributor II

Re: Subqueries - script

Hi Raul,

Please select correct/helpful answers and close the thread. For your new query, please start new thread. Thank you.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Subqueries - script

Set analysis on 10 M records itself isn't a big problem even with rather small hardware-ressources. By aggr-functions instead could be this different - but this wasn't suggested from jagan. If you need max. gui-performance you will need to create boolean flag-fields within the script to use expressions like count(value*flagfield), see also: Customized flags. If this isn't enough you need to reduce the amount of records, number of fields and/or complexity from your application.

- Marcus