Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
-
Hi,
Can you please provide the data file.
Regards,
Kaushik Solanki
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,
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,
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
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
-
Many thanks to all of you, it worked!