2 Replies Latest reply: Nov 2, 2012 6:08 AM by Eduard Cabanas Gili RSS

    Calculate a fiel in a table for all rows

    Eduard Cabanas Gili

      Hi everyone,

       

      I want to calculate two fields from this expression and create a total, and for all rows:

       

      SALESLINE:

      Load UPPER(ITEMID)AS ITEMID,SALESSTATUS,SALESID,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,EVE_AMOUNTMSTEXCLTAX, year(CREATEDDATETIME)AS ANO, Month(CREATEDDATETIME)AS Mes;

      SQL SELECT SALESID,ITEMID,SALESSTATUS,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,CREATEDDATETIME,EVE_AMOUNTMSTEXCLTAX

      from DynamicsAx.dbo.SALESLINE;

      Concatenate (SALESLINE)

       

       

      LOAD UPPER(ITEMID) AS ITEMID,SALESID,COSTAMOUNTPOSTED,year(INVOICEDATE)AS ANO, Month(INVOICEDATE)AS Mes;

      SQL SELECT ITEMID,SALESID,COSTAMOUNTPOSTED,INVOICEDATE

      FROM DynamicsAx.dbo.EVE_CUSTINVOICETRANS;

       

      Now I want to calculate QTYORDERED * COSTAMOUNTPOSTED  for any row resultant

       

      LET A=NoOfRows('SALESLINE');

      for couter=1 to A

       

      TOTAL= QTYORDERED * COSTAMOUNTPOSTED

       

      A=A+1

      NEXT

       

      thank's for your patience

       

      Eduard

        • Re: Calculate a fiel in a table for all rows
          Michael Jordan

          Hi Eduard,

           

          Can you try the code below

           

          SALESLINE_TEMP:

          Load UPPER(ITEMID)AS ITEMID,SALESSTATUS,SALESID,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,EVE_AMOUNTMSTEXCLTAX, year(CREATEDDATETIME)AS ANO, Month(CREATEDDATETIME)AS Mes;

          SQL SELECT SALESID,ITEMID,SALESSTATUS,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,CREATEDDATETIME,EVE_AMOUNTMSTEXCLTAX

          from DynamicsAx.dbo.SALESLINE;

          Concatenate (SALESLINE)


          JOIN (SALESLINE_TEMP)
          LOAD UPPER(ITEMID) AS ITEMID,SALESID,COSTAMOUNTPOSTED,year(INVOICEDATE)AS ANO, Month(INVOICEDATE)AS Mes;

          SQL SELECT ITEMID,SALESID,COSTAMOUNTPOSTED,INVOICEDATE

          FROM DynamicsAx.dbo.EVE_CUSTINVOICETRANS


          SALESLINE:
          NOCONCATENATE
          LOAD
          *,
          (QTYORDERED * COSTAMOUNTPOSTED) AS TOTAL
          RESIDENT SALESLINE_TEMP;

          DROP TABLE SALESLINE_TEMP;

           

           

          Thanks

          Amar

            • Re: Calculate a fiel in a table for all rows
              Eduard Cabanas Gili

              Hi Amar,

               

              your suggetsion:

              SALESLINE_TEMP:

              Load UPPER(ITEMID)AS ITEMID,SALESSTATUS,SALESID,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,EVE_AMOUNTMSTEXCLTAX, year(CREATEDDATETIME)AS ANO, Month(CREATEDDATETIME)AS Mes;

              SQL SELECT SALESID,ITEMID,SALESSTATUS,QTYORDERED,INVENTTRANSID,CUSTACCOUNT,CREATEDDATETIME,EVE_AMOUNTMSTEXCLTAX

              from DynamicsAx.dbo.SALESLINE;

              Concatenate (SALESLINE_TEMP)

               

               

              JOIN (SALESLINE_TEMP)

              LOAD UPPER(ITEMID) AS ITEMID,SALESID,COSTAMOUNTPOSTED,year(INVOICEDATE)AS ANO, Month(INVOICEDATE)AS Mes;

              SQL SELECT ITEMID,SALESID,COSTAMOUNTPOSTED,INVOICEDATE

              FROM DynamicsAx.dbo.EVE_CUSTINVOICETRANS

               

               

              SALESLINE:

              NOCONCATENATE

              LOAD *,(QTYORDERED * COSTAMOUNTPOSTED) AS TOTAL

              resident SALESLINE_TEMP;

              DROP TABLE SALESLINE_TEMP;

               

               

              It returns an error 

              "Sufix combination error"

              Concatenate (SALESLINE_TEMP)

               

               

              JOIN (SALESLINE_TEMP)

              LOAD UPPER(ITEMID) AS ITEMID,SALESID,COSTAMOUNTPOSTED,year(INVOICEDATE)AS ANO, Month(INVOICEDATE)AS Mes

               

               

               

               

              and...abusing your confidence, what could I do If I want to check the fiel Salesstatus for every row, if salesstatus ="" then "1"

               

              Thank's

               

              Eduard