I have to make a calculation that shows me for each sales order, how much is the freight charge.
The problem is that for each transportation made, I can find more than one sales order associated, and when it occurs, I have to calculate the corresponding freight charge according with the weight of the products for the sales order, and I have no idea how can I do this using Qlik View.
Here are the tables that I have:
Freight Document
Freight Number (key)
Amount
Weight
Sales Order
Order Number (key)
Freight Number (key)
Weight
In a normal application, I'd read all sales order for each freight document, and make the prorate according to the sales order weight to find the corresponding amount, and then I could know the corresponding freight amount for each sales order.
If you're instead loading from QVDs or other existing QlikView tables, I think it gets a bit more involved, since you can only load from one table at a time. Here's one approach:
LEFT JOIN ([Freight Document]) LOAD "Freight Number" ,sum("Weight") as "Freight Weight" RESIDENT [Sales Order] GROUP BY "Freight Number" ; [Freight Unit Price]: MAPPING LOAD "Freight Number" ,"Amount" / "Freight Weight" as "Freight Unit Price" RESIDENT [Freight Document] ; DROP FIELD "Freight Weight" ; LEFT JOIN ([Sales Order]) LOAD "Order Number" ,"Weight" * applymap('Freight Unit Price',"Freight Number") as "Freight Amount" RESIDENT [Sales Order] ;
