topic Re: USE WHERE EXIST() in Water Cooler

USE WHERE EXIST()

Wed, 03 Sep 2014 21:00:35 GMT

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??????

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:02:55 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:08:23 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:09:08 GMT

and thanks for comment 😃

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:12:34 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:16:26 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:28:08 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 21:42:59 GMT

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

Re: USE WHERE EXIST()

Wed, 03 Sep 2014 22:04:23 GMT

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

Re: USE WHERE EXIST()

evan_kurowski — Thu, 04 Sep 2014 04:30:43 GMT

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];