Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
c_latham
Contributor III
Contributor III

Equivalent of Exists from SQL

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!

1 Reply
Vegar
MVP
MVP

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;