USE WHERE EXIST()

[#1]

HOW CAN I USE WHERE EXIST  HERE:

HPH:

LOAD PHID,

    PHORD, //PurchaseOrd_Id,

    PHCOMP, // Compañía

    PHFAC, // Facility

    PHWHSE, // Almacen

    PHVEND, // Proveedor

    PHSHTP, // Ship To

    PHSHIP, // Ship To #

    PHNAME, // Nombre

    PHATTN, // Atencion

    PHENDT, // Fecha Entrada

    PHTERM, // Termino de Pago

    PHBUYC, // Comprador

    PHCUR, // Moneda

    PHRQID, // Usuario Requisicion

    PHLAID, // Usuario Aprobacion

    PHAPDT, // Fecha Aprobacion

    PHVTXC, // Codigo de Impuesto

    PHCRCC, // Pais

    PHCRNO, // RFC BELLOTA

    PHSRCC, // Pais proveedor

    PHSRNO, // RFC Proveedor

    PHCMT; //Fecha de Embarque

SQL SELECT *

FROM F60CB75B.MP61BPCSF.HPH

WHERE PHENDT >= '20140901' and  PHENDT <= '20140903';

HPO:

LOAD PID,

     PORD ,//as %PurchaseOrd_Id,

     PLINE ,//AS  PO_Line,

     PPROD ,//AS %ProductoId,

     PQORD ,//AS ME_PO_Unit,

     PDDTE ,//AS  PO_Date_RQST_DLVR, 

     PUM ,//AS  PO_UM, 

     PECST,//as [Precio U]

     PUMCN,// AS ME_PO_UM_CVTR, 

     PWHSE ,// AS  %Ship_Id,

     PGEXRT;//  AS ME_PO_Tipo_Cambio

SQL SELECT *

FROM F60CB75B.MP61BPCSF.HPO

WHERE PDDTE >= '$(vAño)' and  PDDTE <= '$(vHoy)';   <<<<-----------------HOW CAN I USE HERE WHERE EXIST??????

[#2]

Hi

wwhy do you want to use a the exists function here?are you using s simple where clause to filter on dates?

[#3]

I'm trying not to use the dates and use only "Exist" in the previous table

[#4]

and thanks for comment 😃

[#5]

Ok so in the where statement of the 2nd table you would use the following statement :

where exists (field in first table,field in second table)

this would check against previously loaded values in table 1 & match them to table 2 in those fields specified in the exists function.

thanks

[#6]

good info on exists function here Understanding EXISTS() function - Qlikview vs SQL/RDBMS

[#7]

I had read that url, but not working for me, I think it's for the SQL connector. I get this error:

[#8]

The issue is that exits() function syntax is not the same in SQL and qlikview so will cause an error,you can only use it in QV tables.probably the best way to do this in SQL would be as per this syntax

USE AdventureWorks2012 ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE EXISTS
(SELECT *
  FROM HumanResources.Employee AS b
  WHERE a.BusinessEntityID = b.BusinessEntityID
  AND a.LastName = 'Johnson');
GO

however would be easier to do in the Qv tables in my opinion

[#9]

yeah i use in QV load, but here i cant use =(

[evan_kurowski]

Hello baltazar,

The following will allow you to filter via a WHERE EXISTS on your two load statements.  However, I would point out that since the original load statements are SQL from your native RDMS systems, there is probably no processing savings and likely even worse performance from using WHERE EXISTS in this situation.

If you remove the filtration condition from the SQL side and shift it to the QlikView side preceding load, the SQL statement is going to pass all values for your filter fields across the Connection, and then only after all the data related to your entire data set in both tables has been brought over to QlikView, only then will the WHERE EXISTS reduce the results based on matching exists values.

So if HPH and HPO are very large tables, you are much better off for extraction times and resource usage in applying the filter on the native DB syntax.  In this scenario here, it doesn't look like WHERE EXISTS will help you.

WHERE EXISTS used under the right conditions will produce an optimized load that is many times faster than ODBC/OLE data transfer rates, but the data must already exist in QVD form before that savings can be realized.

Imagine the first time you are reading a very large table and you spend the initial time exporting the whole business to QVD, on the second pass, if there are portions of the data that you extracted to QVD that can be reused, THEN you can capitalize on WHERE EXISTS optimized loads.

(one caveat... I have used an inefficient Qlikview side WHERE clause in place of an RDMS filter in cases where the WHERE CLAUSE syntax on the SQL statement is not interpreting in a consistent manner.  For example, I had a database load that every time the WHERE clause date range on the SQL side was worked out, something would change on the database side and the query would begin returning the entire table, because changing field attributes kept invalidating the filter.  Without being able to control the date filtration field on the DB side, taking matters onto the QlikView side allowed sidestepping disruption from the shifting DB attributes.  The load times were definitely longer, but the filter never broke from that point forward)

Anyway, here's a version of your script with WHERE EXISTS performing the filtration.

[FILTER_HPH]:
LOAD * INLINE [
PHENDT
20140901
20140902
20140903
];

HPH:
LOAD PHID,
PHORD, //PurchaseOrd_Id,
    PHCOMP, // Compañía
    PHFAC, // Facility
    PHWHSE, // Almacen
    PHVEND, // Proveedor
    PHSHTP, // Ship To
    PHSHIP, // Ship To #
    PHNAME, // Nombre
    PHATTN, // Atencion
    PHENDT, // Fecha Entrada
    PHTERM, // Termino de Pago
    PHBUYC, // Comprador
    PHCUR, // Moneda
    PHRQID, // Usuario Requisicion
    PHLAID, // Usuario Aprobacion
    PHAPDT, // Fecha Aprobacion
    PHVTXC, // Codigo de Impuesto
    PHCRCC, // Pais
    PHCRNO, // RFC BELLOTA
    PHSRCC, // Pais proveedor
    PHSRNO, // RFC Proveedor
    PHCMT//Fecha de Embarque
    WHERE EXISTS (PHENDT, PHENDT)
;
SQL SELECT *
FROM F60CB75B.MP61BPCSF.HPH
//WHERE PHENDT >= '20140901' and  PHENDT <= '20140903'
;

[FILTER_HPO]:
LOAD $(vAno) + IterNo() - 1 AS PDDTE
AUTOGENERATE 1 WHILE $(vAno) + IterNo() - 1  <= $(vHoy);


HPO:
LOAD PID,
PORD ,//as %PurchaseOrd_Id,
     PLINE ,//AS  PO_Line,
     PPROD ,//AS %ProductoId,
     PQORD ,//AS ME_PO_Unit,
     PDDTE ,//AS  PO_Date_RQST_DLVR, 
     PUM ,//AS PO_UM, 
     PECST,//as [Precio U]
     PUMCN,// AS ME_PO_UM_CVTR, 
     PWHSE ,// AS  %Ship_Id,
     PGEXRT// AS ME_PO_Tipo_Cambio
     WHERE EXISTS (PDDTE,PDDTE)
;
SQL SELECT *
FROM F60CB75B.MP61BPCSF.HPO
//WHERE PDDTE >= '$(vAño)' and  PDDTE <= '$(vHoy)'
;  
//<<<<-----------------HOW CAN I USE HERE WHERE EXIST??????

DROP TABLES [FILTER_HPH], [FILTER_HP0];