topic Re: Subqueries - script in QlikView

Subqueries - script

rulohx87 — Sat, 29 Aug 2015 05:00:44 GMT

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)

Re: Subqueries - script

fashid — Sat, 29 Aug 2015 05:22:26 GMT

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

Re: Subqueries - script

qlikviewwizard — Sat, 29 Aug 2015 07:11:01 GMT

Hi Raul,

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

Re: Subqueries - script

sasiparupudi1 — Sat, 29 Aug 2015 07:39:16 GMT

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

Re: Subqueries - script

Anonymous — Sat, 29 Aug 2015 08:51:29 GMT

Hi Raul,

Please can you post sample data.

Regards

Neetha

Re: Subqueries - script

marcus_sommer — Sat, 29 Aug 2015 09:56:40 GMT

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

Re: Subqueries - script

jagan — Sat, 29 Aug 2015 10:18:25 GMT

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.

Re: Subqueries - script

rulohx87 — Sat, 29 Aug 2015 18:50:36 GMT

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

Re: Subqueries - script

qlikviewwizard — Sun, 30 Aug 2015 02:00:53 GMT

Hi Raul,

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

Re: Subqueries - script

marcus_sommer — Sun, 30 Aug 2015 09:44:34 GMT

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

Re: Subqueries - script

Anonymous — Mon, 31 Aug 2015 17:23:41 GMT

hi raul,

if you are using jagan's script and left joining both the tables, there's no need to rename field PEsCve to PES_CVE,

try keeping both fields name same and try.

SCRIPT:

LOAD

CURP,

CiEgIni,

PEsCve,

EAGCve,

AlGProme

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

ALUMATER:

LOAD

CURP,

CiEMInici,

PEsCve AS PES_CVE, // keep as PEsCve

AMaPromedi

FROM

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

UNQUALIFY * ;

Regards

Neetha