Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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)
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
Thats the solution, Thankyou!!