Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rafaeloneil
Contributor III
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
maxgro
MVP
MVP

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;

reddy-s
Master II
Master II

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
Partner - Creator
Partner - Creator

‌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.

jagan
Luminary Alumni
Luminary Alumni

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.