Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

salto
Not applicable

Calculate Invoice Amount in Invoice Header

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.

1 Solution

Accepted Solutions
rwunderlich
Not applicable

Calculate Invoice Amount in Invoice Header

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

-

6 Replies
kaushik_solanki
Not applicable

Calculate Invoice Amount in Invoice Header

Hi,

    Can you please provide the data file.

Regards,

Kaushik Solanki

rrs
Not applicable

Calculate Invoice Amount in Invoice Header

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
Not applicable

Calculate Invoice Amount in Invoice Header

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,

Not applicable

Calculate Invoice Amount in Invoice Header

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
Not applicable

Calculate Invoice Amount in Invoice Header

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
Not applicable

Calculate Invoice Amount in Invoice Header

Many thanks to all of you, it worked!