Equivalent of Exists from SQL

c_latham — Mon, 20 Dec 2021 21:08:46 GMT

Hi All,

I have inherited someones SQL and I am in the process of creating a QS App which would suit moving this piece of code into.

SELECT DISTINCT COUNT(VISIT_ID) as result1 from VISIT v WHERE v.ATA BETWEEN '2020-02-11 00:00:00' AND '2020-02-18 23:59:59' AND EXISTS (select VISIT_ID from SNAPSHOT s where v.VISIT_ID = s.VISIT_ID and s.LANE = '1' and s.CARGO_TYPE = 'TANK') AND EXISTS (select VISIT_ID from VISITLOG vl where v.VISIT_ID = vl.VISIT_ID and vl.SOURCE = 'IN' and vl.EVENT = 'LOW_CONF');

This returns a single value of 50.

I can do the equivalent of each sub-query in Qlik (I have all the raw tables, a fact table and all joins are correct and working).

I am looking for some advice as to what is the best way to tackle something like the above in QlikSense.

Thanks!

Re: Equivalent of Exists from SQL

Vegar — Thu, 23 Apr 2020 13:34:08 GMT

Try something like this.

TANK_VISIT_IDs:
LOAD
VISIT_ID as TANK_VISIT_ID
from SNAPSHOT.qvd (qvd)
LANE = '1' and CARGO_TYPE = 'TANK';

LOW_CONF_VISIT_IDs:
LOAD VISIT_ID as LOW_CONF_VISIT_ID
from VISITLOG.qvd (qvd)
where
SOURCE = 'IN' and EVENT = 'LOW_CONF';

LOAD DISTINCT
COUNT(VISIT_ID) as result1
from VISIT.QVD
WHERE
ATA >='2020-02-11 00:00:00' AND ATA <='2020-02-18 23:59:59' AND
Exists(TANK_VISIT_ID,VISIT_ID) AND
Exists(LOW_CONF_VISIT_ID, VISIT_ID)
;

DROP TABLE TANK_VISIT_IDs;
DROP TABLE LOW_CONF_VISIT_IDs;

topic Re: Equivalent of Exists from SQL in App Development

Equivalent of Exists from SQL

Re: Equivalent of Exists from SQL