9 Replies Latest reply: Sep 4, 2014 12:58 AM by Evan Kurowski RSS

    USE WHERE EXIST()

    baltazr covarrubia

      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()

          Hi

           

          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()
            baltazr covarrubia

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

            • Re: USE WHERE EXIST()
              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];