Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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
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.
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.