Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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
Hi Raul,
Bring these tables into datamodel and post the script. We can achieve what ever we want in QV. Thank you.
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
Hi Raul,
Please can you post sample data.
Regards
Neetha
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
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.
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 * ;
Hi Raul,
Please select correct/helpful answers and close the thread. For your new query, please start new thread. Thank you.
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