2 Replies Latest reply: Dec 18, 2015 12:23 PM by Adam Krepistman RSS

    WHERE Clause for both tables

    Matt Patterson

      I want to add a where clause that uses both tables.

       

      this statement loads fine

       

      TABA:

      LOAD [TABA.BNumber] AS [Number],

           [TABA.Status],

           [TABA.I Number] AS [I No],

           [TABA.reference] AS [App Ref],

           [TABA.InvoiceType] AS [InvoiceType]

      FROM

      D:\QlikView\QVD\TABA\TABA.QVD

      (qvd);

       

      LEFT JOIN(TABA)

       

      IFS:

      LOAD [TABB.I Number] AS [B I Number],

           [TABB.IFS I Amount],

           [TABB.Name]  AS [Name],

           [TABB.DEL] AS [Del Locations],

           [TABB.MIS I Ref] AS [I No]

      FROM

      D:\QlikView\Live\QVD\TABB\TABB.QVD

      (qvd)

      WHERE PurgeChar(TABB.Name,'*') >= '33000';

       

      I want to add something like TABA.InvoiceType <> LEFT(TABB.DEL,5) but i get an error.

       

      how can i do this

        • Re: WHERE Clause for both tables
          Sunny Talwar

          May be this:

           

          TABA:

          LOAD [TABA.BNumber] AS [Number],

              [TABA.Status],

              [TABA.I Number] AS [I No],

              [TABA.reference] AS [App Ref],

              [TABA.InvoiceType] AS [InvoiceType]

          FROM

          D:\QlikView\QVD\TABA\TABA.QVD

          (qvd);

           

          LEFT JOIN(TABA)

          LOAD [TABB.I Number] AS [B I Number],

              [TABB.IFS I Amount],

              [TABB.Name]  AS [Name],

              [TABB.DEL] AS [Del Locations],

              [TABB.MIS I Ref] AS [I No]

          FROM

          D:\QlikView\Live\QVD\TABB\TABB.QVD

          (qvd)

          WHERE PurgeChar(TABB.Name,'*') >= '33000';

           

          TABA1:

          NoConcatenate

          LOAD *

          Resident TABA

          Where TABA.InvoiceType <> Left(TABB.DEL,5);


          DROP Table TABA;

          • Re: WHERE Clause for both tables
            Adam Krepistman

            you will need to do a resident load of the TABA table in order to use the where since the field you are using in your where clause has not ben defined yet

             

            or you can try

             

            Load *
            // loads data for specific invoice types
            where TABA.InvoiceType <> LEFT(TABB.DEL,5)

             

            prior to your table load