Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
See attached example.
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 |
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;
but how to get Pending Comment there...
See attached qvw