Calculate a fiel in a table for all rows

[ecabanas]

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

[amars]

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

[ecabanas]

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