Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rafaeloneil
New Contributor III

Question About Script With "Where Exists"

Hello Guys, concerning the use of "Where Exists" in the scripts I have a question:

I have 2 tables:

(A)

LIB CONNECT TO 'DBGESTAOCP';

LOAD NuContrato, DtContrato, 'DBGESTAOCP' AS 'indicator';

SQL SELECT NuContrato, DtContrato

dbo.TbContrato FROM

WHERE DtContrato BETWEEN '20160101' AND '20160126';

(B)

LIB CONNECT TO 'DBPENDENCIA';

LOAD NuContrato,

    CdTipoEstadoEnvelope,

    DtEntrega,

    DtRecebimento,

    DtPrimeiraFormalizacao;

SQL SELECT NuContrato,

    CdTipoEstadoEnvelope,

    DtEntrega,

    DtRecebimento,

    DtPrimeiraFormalizacao

FROM dbo.TbEnvelope;

Question:

I need that table B only load up data where B.NuContrato = A.Nucontrato.

Because Table B has many records!

How to properly use the "Where Exists " in this case ?

Thank you!

4 Replies
MVP
MVP

Re: Question About Script With "Where Exists"

try adding the bold

LIB CONNECT TO 'DBPENDENCIA';

LOAD NuContrato,

    CdTipoEstadoEnvelope,

    DtEntrega,

    DtRecebimento,

    DtPrimeiraFormalizacao

where exists (NuContrato)

;

SQL SELECT NuContrato,

    CdTipoEstadoEnvelope,

    DtEntrega,

    DtRecebimento,

    DtPrimeiraFormalizacao

FROM dbo.TbEnvelope;

reddys310
Honored Contributor II

Re: Question About Script With "Where Exists"

Hi Rafael,

This can be achieve in two ways:

1) by using the resident / preceding load as M G mentioned.

2) you could have even used an inner join in the sql query itself.

LIB CONNECT TO 'DBPENDENCIA';

Load *;

SQL SELECT B.NuContrato,

    B.CdTipoEstadoEnvelope,

    B.DtEntrega,

    B.DtRecebimento,

    B.DtPrimeiraFormalizacao

FROM dbo.TbEnvelope B inner join

dbo.TbContrato A ON B.NuContrato = A.Nucontrato.

WHERE A.DtContrato BETWEEN '20160101' AND '20160126';

;


Thanks,

Sangram

dominicmander
Contributor

Re: Question About Script With "Where Exists"

‌The disadvantage of using the where exists approach in this scenario is that it executes in Qlik after all the records have loaded from the database, so you don't save yourself the time of loading all the records from the database.

But, if the load is relatively quick anyway you may use fewer resources of the database server by not performing the join in sql.

MVP
MVP

Re: Question About Script With "Where Exists"

Hi,

You can try LEFT KEEP option for this like below

Table1:

LOAD

*

FROM DataSource1;

Table2:

LEFT KEEP(Table1)

LOAD

*

FROM DataSource2;

HOpe this helps you.  This is similar to WHERE Exists().

Regards,

jagan.

Community Browser