4 Replies Latest reply: Nov 13, 2015 3:31 PM by James Hanify RSS

    Cross referencing a field between 2 qvds based on a criteria

    James Hanify

      Hi,

       

      We have credits and invoices as part of our load script from the QVDs.

       

      What I would like to do for the LINECOST in credits, is to say If linecost = 0 or null then take the LINECOST from the invoice, is this easily possible? What I was thinking of doing is WHERE LINECOST <> 0 but then it would just be filtering out any with linecost doesn't equal zero which isn't exactly what i'd like. I have been looking at crosstable but i'm not sure if this is really along the same lines.

       

      Credits:

       

      Concatenate (Transactions)

      LOAD TransactionID,
      Customer,
      TransactionType,
      "INV_NO",
      InvoiceNumber,
      "SALESM_CODE",
      TransactionDate,
      CreditDate,
      WAREHOUSE
      FROM
      Credits.QVD
      (
      qvd);

      Concatenate  (TransactionDetails)

      LOAD TransactionID,
      ITEM,
      LINECOST,
      NetSalePrice,
      QuantityInvoiced
      FROM
      CreditItems.QVD
      (
      qvd);

       

       

      Invoices:

       


      Transactions:

      LOAD Invoices,
      TransactionType,
      TransactionID,
      InvoiceID,
      Customer,
      TransactionDate,
      InvoiceDate,
      InvoiceNumber,
      SALESM_CODE,
      PONUM
      FROM
      Hist*Transactions.QVD
      //Load all history
      //Hist201*Transactions.QVD //Load only history starting with 201 - ie 2010 onwards
      (qvd);

      Concatenate

      LOAD Invoices,
      TransactionType,
      TransactionID,
      InvoiceID,
      Customer,
      TransactionDate,
      InvoiceDate,
      InvoiceNumber,
      SALESM_CODE,
      WAREHOUSE,
      CONSULTANT_NAME_C,
      PONUM
      FROM
      Transactions.QVD
      (
      qvd);



      TransactionDetails:
      // PT added v 0.2 - all fields loaded already

      LOAD TransactionID,
      ITEM,
      QuantityInvoiced,
      NetSalePrice,
      LINECOST,
      CRT,
      Z_ASSOC_ROW
      FROM
      Hist*TransactionDetails.QVD
      //Hist201*TransactionDetails.QVD
      (qvd);


      Concatenate

      LOAD TransactionID,
      ITEM,
      QuantityInvoiced,
      NetSalePrice,
      LINECOST,
      CRT,
      Z_ASSOC_ROW
      FROM
      TransactionDetails.QVD
      (
      qvd);




        • Re: Cross referencing a field between 2 qvds based on a criteria
          Massimo Grossi

          - load the invoices

           

          - create a mapping table from the invoices table

          Map:

          mapping load

          FIELD, LINECOST

          resident TransactionsDetails;

          replace FIELD with the join field (or fields using field1 & '-' & field2)  between invoices and credits

           

          - load credits using the mapping table

          LOAD TransactionID,

          ITEM,
          if(len(trim(LINECOST))=0, ApplyMap('Map', FIELD, LINECOST) as LINECOST
          NetSalePrice,
          QuantityInvoiced
          FROM CreditItems.QVD (qvd
          );



            • Re: Cross referencing a field between 2 qvds based on a criteria
              James Hanify

              Hi Max,

               

              Many thanks for the reply, I botched another field that I can use to combine them as i'm not sure how they are concatenated against for the minute.

               

              I do get some errors when I do it,

               

              Table not found

              Map:

              mapping load

              INV_NO, LINECOST resident TransactionalDetails

               

              And

               

              Error in expression:

              ')' expected

              Concatenate  (TransactionDetails)

               

               

              LOAD TransactionID,

                  ITEM,

                  if(len(trim(LINECOST))=0, ApplyMap('Map', INV_NO, LINECOST) as LINECOST

                  NetSalePrice,

                  QuantityInvoiced

              FROM

              CreditItems.QVD

              (qvd)

               

              I also think it is technically null as it is showing as - in the table view, maybe I told you the wrong information

                • Re: Cross referencing a field between 2 qvds based on a criteria
                  Massimo Grossi

                  I think you miss a bracket, a comma and the else part of the if (LINECOSt > 0)

                   

                  LOAD TransactionID,

                      ITEM,

                      if(len(trim(LINECOST))=0, ApplyMap('Map', INV_NO, LINECOST)  as LINECOST

                      NetSalePrice,

                      QuantityInvoiced

                  FROM

                  CreditItems.QVD

                  (qvd)

                   

                   

                  try with

                   

                  LOAD TransactionID,

                      ITEM,

                  if  (len(trim(LINECOST))=0,

                            ApplyMap('Map', INV_NO, LINECOST),          /* LINECOST=0, try to get from mapping table using INV_NO */

                            LINECOST                                                  /* LINECOST <>0, use LINECOST */

                      )  as LINECOST,

                      NetSalePrice,

                      QuantityInvoiced

                  FROM CreditItems.QVD (qvd);


                  there is a INV_NO field  in CreditItems.qvd ?