Calculate Invoice Amount in Invoice Header

[salto]

Hello,

In the data source, there is no Amount field in the Invoice Header, it is calculated by making a sum of the Sales Lines table "Amount" field.

But I would like to get that Invoice Total in the initial script (I can do it in the charts, but I'd like to have it in the script).

Is there any aggregation function I could use for this purpose? Many thanks in advance.

[kaushiknsolanki]

Hi,

    Can you please provide the data file.

Regards,

Kaushik Solanki

[RicardoRamos]

Hi,

you can load the invoice detail, after that create a temporary table with InvoiceID and Sum(amount) and use that table to do a left join with the header table.

Regards,

[salto]

Hello Ricardo,

     Thank you. Which function should I use to Sum the total for every InvoiceID? This seems no to be correct:

AuxTotals:
load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines;

Regards,

[#5]

Hi

AuxTotals:
load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines

group by InvoiceID ;

That should solve your problem..use group by

Regards

sravan

[rwunderlich · Accepted solution]

You can use an anonymous table in the join and skip creating and dropping the extra table. For example:

LEFT JOIN (InvoiceHeader)

load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines

group by InvoiceID ;

-Rob

-

[salto]

Many thanks to all of you, it worked!