Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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