I am relatively new to QlikView and am having an issue when joining two tables. In my load script, I have an orders table and a shipment table. From orders, I am creating a sum of an order quantity field and receipt quantity field. In my shipment table, I am creating a sum of a shipped quantity field. Seperate, both work perfect. However, when I join the tables, I am getting a duplication. I have been able to identify why this is happening, from a data perspective, but have not been able to resolove it in QV.
The reason behind the duplication is that on the shipment table, I have the following data:
ORDER NO
LINE NO
INVOICE NO
ENTRY NO
I am joining the two tables on ORDER NO and LINE NO, which in 99% of the orders, it works fine. However, there are times when an order line # can have multiple shipment records in the shipment table, with distinct entry/invoice #'s. For example:
SHIPMENT:
ORDER_NO LINE_NO ENTRY_NO INVOICE_NO SHIP QTY
69589 2 1719820-4 SSL228679 150
69589 2 1719821-2 SSL228678 50
69589 1 1720225-3 SSL228669 150
69589 1 1720224-6 SSL228677 250
For that ORDER #, the orders table looks like this:
ORDER:
ORDER_NO LINE_NO ORDER QTY RECEIPT QTY
69589 2 200 100
69589 1 500 400
When I left join orders to shipment, the results I am getting are duplicating on the order side. This is happening because the LINE NO values in shipment do not match exactly with order, as there can be multiple shipments from an order line. Post join, there is a duplicate row of order.line_no created for line 1 and 2 and it duplicates the order qty and receipt qty.
ORDER_NO LINE_NO ORDER QTY RECEIPT QTY
69589 2 200 100
69589 2 200 100
69589 1 500 400
69589 1 500 400
Essentially, on the order side I am getting a doubled sum total for order and receipt qty because of the multiple shiment lines with distict entry and invoice values.
Can anyone offer any insight into a fix in my scripting?
Entry no and invoice no need added for an additional join to an invoice table, which I have built seperately. The dashboard contains a pivot that shows summed order qty, receipt qty, shipped qty, and entry amount for a fiscal month year. In order to get this all included in the table there are a number of tables that need to be included, invioce being one which is the reason for need of entry and invoice no's to be added.
Biggest mistake in your script is a concatination of Orders and Shipments. You are loosing relationship between orders and shipments. Download the fixed script and reload it.
To avoid synthetic key you can concatinate columns
PO & '-' & [PO Row] as OrderLineId
in OrderPrep and ShipPrep tables. But this is optional in your situation.
