6 Replies Latest reply: Oct 14, 2016 7:10 AM by Court van de Lisdonk RSS

    Exists or Not Exists

    Court van de Lisdonk

      Why does this not work: I load a qvdfile into a table HstDat_GLEntry, this table I join with my measure HstDat_SalesInvoice.

       

      After the Join I load data into a table Map_IncludingRevenueAccounts  from an excel file. After this load I want to clean up my masterfile:

       

      I make a new table (SalesInvoiceLedger) from the existing table (HstDat_SalesInvoice) when AX_GLAccountNumber exists in HstDat_SalesInvoice as GLAccountID

       

      The statement is Resident HstDat_SalesInvoice
      where EXISTS (AX_GLAccountNumber, GLAccountID);

       

      When I reload my script I have no records left and my new table SalesInvoiceLedger is empty.

       

      When I change the statement from Where Exists in Where not exists all the numbers in my Excelfile are not loaded in my new table

       

      Here below is my script:

       

       

       

      /*  Historical Data Loading  */
      HstDat_GLEntry:
      noconcatenate 
      LOAD
      Company & DataLevel  & VoucherNumber    as  GLAccountIDKey,
      Company,
      DataLevel,
      LoadingDate,
      GLAccountID,
      PostingDate,
      //     VoucherNumber,
      VoucherNumber       as InvoiceNumber,
      SubLedgerVoucher
      FROM [$(DATADIR)qvd\HstDat_GLEntry.qvd] (qvd)
      ;

      HstDat_SalesInvoice: LOAD
              Company,
      DataLevel,
      LoadingDate,
      InvoiceNumber,
      PostingDate,
      Year(PostingDate)             as Year,
      Month(PostingDate)    as Month,
      InvoiceLineNumber,
      LineAmount,
      CustomerID,
      FROM [[$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)
      ;

      // a Left Join with HstDat_SalesInvoice
      Left Join (HstDat_SalesInvoice) Load

      *
      Resident HstDat_GLEntry; Drop Table HstDat_GLEntry;

      Map_IncludingRevenueAccounts:
      noconcatenate
      Load
             AX_GLAccountNumber,
      Active
      FROM [$(DATADIR)excel\Umsatzbildung001.xlsx]
      (ooxml, embedded labels, table is including_RevenueAccounts)
      where Active = 1;

      SalesInvoiceLedger:
      noconcatenate 
      LOAD
             *
      Resident HstDat_SalesInvoice
      where EXISTS (AX_GLAccountNumber, GLAccountID); DROP Table HstDat_SalesInvoice;
      exit script;

       

      Hope someone can help me or give me a hint what I do wrong

      regards

       

        • Re: Exists or Not Exists
          Ramesh Katla

          Can you try with following script, This might solve your problem.

           

          Map_IncludingRevenueAccounts:

          noconcatenate Load

          AX_GLAccountNumber,

          Active

          FROM [$(DATADIR)excel\Umsatzbildung001.xlsx]

          (ooxml, embedded labels, table is including_RevenueAccounts)

          where Active = 1;

           

          /*  Historical Data Loading  */

          HstDat_GLEntry:

          noconcatenate 

          LOAD

          Company & DataLevel  & VoucherNumber    as  GLAccountIDKey,

          Company,

          DataLevel,

          LoadingDate,

          GLAccountID,

          PostingDate,

          //     VoucherNumber,

          VoucherNumber       as InvoiceNumber,

          SubLedgerVoucher

          FROM [$(DATADIR)qvd\HstDat_GLEntry.qvd] (qvd)

          where EXISTS (AX_GLAccountNumber, GLAccountID)

           

           

          ;

          SalesInvoiceLedger:

          LOAD

                  Company,

          DataLevel,

          LoadingDate,

          InvoiceNumber,

          PostingDate,

          Year(PostingDate)             as Year,

          Month(PostingDate)    as Month,

          InvoiceLineNumber,

          LineAmount,

          CustomerID,

          FROM [[$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)

          ;

          // a Left Join with HstDat_SalesInvoice

          Left Join (HstDat_SalesInvoice) Load

          *

          Resident HstDat_GLEntry;

           

           

          Drop Table HstDat_GLEntry;

          • Re: Exists or Not Exists
            Marcus Sommer

            The changed load-structure from Ramesh should work with a small adjustment:

             

            Map_IncludingRevenueAccounts:

            noconcatenate Load

            AX_GLAccountNumber,

            Active

            FROM [$(DATADIR)excel\Umsatzbildung001.xlsx]

            (ooxml, embedded labels, table is including_RevenueAccounts)

            where Active = 1;

             

            /*  Historical Data Loading  */

            HstDat_GLEntry:

            noconcatenate 

            LOAD

            Company & DataLevel  & VoucherNumber    as  GLAccountIDKey,

            Company,

            DataLevel,

            LoadingDate,

            GLAccountID,

            PostingDate,

            //     VoucherNumber,

            VoucherNumber       as InvoiceNumber,

            SubLedgerVoucher

            FROM [$(DATADIR)qvd\HstDat_GLEntry.qvd] (qvd)

            where EXISTS (AX_GLAccountNumber, Company & DataLevel  & VoucherNumber)

             

            ....

             

            - Marcus

            • Re: Exists or Not Exists
              Jonathan Dienst

              I see no syntax errors (although there could be some optimisation). The problem could be that you are left joining the table with GLAccountID using join key Company, DataLevel, PostingDate and LoadingDate. If there are no matches, then no GLAccountIDs will exist in HstDat_SalesInvoice (they will be null or missing), and a null/missing value never Exists(), so no data is loaded.