Triying to sum amounts in the script from different fields on a mapping load

[#1]

Hi all,

I'm desperate with this one...

I'm using a mapping load to get two kind of amounts (sales price and sales cost):

Then I'm bringing the two fields into other table:

The only fields in common between this files are "letter" and "invoice" (the ones that I'm using for the mapping).

Mi problem is: in the first file (SALES) there is a sub-invoice field; example:

Letter	Invoice	Subinvoice	FOB€	COST€
A	1405	01	1.200€	1.050€
A	1405	02	456€	432€
A	1405	03	926€	817€
A	1405	04	621€	635€
A	1405	05	325€	323€
A	1405	06	211€	225€
A	1405	07	1.012€	903€

The mapping only gives me the first of this amounts, How can I get the total sum of them?? I need to do this in the script!!

Note that I'm using an ODBC database, I'm not using excel or access files.

[geert_gelade · Accepted solution]

In you mapping tables, you can sum the amounts and group by InvoiceNumber

SALESFOB:

MAPPING LOAD INVOICENUMBER, sum(FOB€)

Resident SALES GROUP BY INVOICENUMBER;

[#3]

Great Geert Gelade!! It works!!

Thank you so much!