Qty Break up in Invoice based on Orders

[MK_QSL]

Hi Experts…..

I have my own customer list and their orders in excel file….

Our system gives me sales/invoice data for all customers.

I want

1. 1) Invoice table to be merged with Order table
   1. a. Only for those customers which are belongs to my account. I don’t want to load full customer sales data
   2. b. No Synthetic Key in this data model as there are many other table which will create another Synthetic Key and want to remove all of them
2. 2) Order and Invoice Table should give me below qty break down table…
3. 3) Also, you can see that for Customer B, I am missing 5pc once the script run.

Customer Name	Part NO	Order Qty	Order Date	Invoice Qty	Invoice Date	Comments
A	ABC	40	01/11/2013	40	10/11/2013	Lot 01
A	ABC	40	01/11/2013	40	15/11/2013	Lot 02
A	ABC	20	01/11/2013			Pending
A	DEF	150	05/11/2013	150	20/11/2013	Lot 01
B	ABC	5	05/11/2013	5	15/11/2013	Lot 01
B	ABC	5	05/11/2013	5	15/11/2013	Lot 02
C	DEF	10	14/11/2013	10	15/11/2013	Lot 01
C	DEF	20	14/11/2013	20	16/11/2013	Lot 02
C	DEF	30	14/11/2013	30	17/11/2013	Lot 03
C	DEF	40	14/11/2013	40		Pending
C	GHI	80	20/11/2013	80	25/11/2013	Lot 01

[Gysbert_Wassenaar]

See attached example.

[MK_QSL]

Dear Gysbert,

You are Genius...!!

Working perfectly...

One more question.. If I am trying to add few more lines in first table (Order table) and considering that not a single pc from that order is invoiced. I can see that the result table is giving two lines for the same.

i.e. If I add

M,

OPQ,

100,

01/11/2013

The result table is giving below two lines for the same..rather than giving single line with comments 'Pending'...

M	OPQ	100	01/11/2013
M	OPQ

[Gysbert_Wassenaar]

You can add an extra where clause to the second join load:

join(Temp2)

load Customer_Name, Part_NO, max(Total_Invoice_Qty) as Total_Invoice_Qty, 1 as Flag Resident Temp2 where Total_Invoice_Qty>0

group by Customer_Name, Part_NO;

[MK_QSL]

but how to get Pending Comment there...

[Gysbert_Wassenaar · Accepted solution]

See attached qvw