different granularity

[thewillemzelluf]

HI all,

i have a problem. I need to sum the total reveneu of an invoice but it consists of 2 components:

1. goods amount

invoice number	reveneu	product_ID
15478	€    40,00	TAT
15478	€    20,55	PTH
15478	€    79,10	JFD
15478	€    88,50	FFT

2. transport

invoice number	transport costs
15478	120

If i do an applymap with the invoice nummers als map_ID it will add transport to all the rows containing the same invoice number. How can i add the transport turnover once per invoice?

thank you in advance!

[vvira1316 · Accepted solution]

I don't think you need to use applymap. Not sure what your data model looks like.

try following

Sum({<invoice number>}revenue) + Sum({<invoice number>}transport costs)

[dwforest]

Note for Vijay's solution you would need to keep the good and transport as separate files, Qlik will avoid the fan trap (multiplying the transport number by the number of goods lines).

Another solution is to categorize the amounts during the load and append transport costs into a single table

Load

Invoice_number, revenue as amount, prooduct_id, 'goods' as amount_type

from goods_table;

Load

invoice_number, tranport_costs as amount, '' as product_id, 'tranport' as amount_type

another approach would be to create a 'product_id' for transport costs

[thewillemzelluf]

Thats the solution, Thankyou!!