Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??????
Hi
wwhy do you want to use a the exists function here?are you using s simple where clause to filter on dates?
I'm trying not to use the dates and use only "Exist" in the previous table
and thanks for comment 😃
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
good info on exists function here Understanding EXISTS() function - Qlikview vs SQL/RDBMS
I had read that url, but not working for me, I think it's for the SQL connector. I get this error:
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
yeah i use in QV load, but here i cant use =(
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];