Subqueries - script

[rulohx87]

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)

[fashid]

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

[qlikviewwizard]

Hi Raul,

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

[sasiparupudi1]

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

[Anonymous]

Hi Raul,

Please can you post sample data.

Regards

Neetha

[marcus_sommer]

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

[jagan · Accepted solution]

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.

[rulohx87]

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

[qlikviewwizard]

Hi Raul,

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

[marcus_sommer]

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