Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
Kindly help me in solving the below issue.
Table1:
Material No | Plant | Qty |
28112 | 0001 | 500 |
1234 | 0002 | 100 |
Table2:
Order Date | KEY No | Plant | Qty_1 |
2-11-2013 | 28112 | 0001 | 300 |
22-11-2013 | 28112 | 0001 | 200 |
24-11-2013 | 28112 | 0001 | 300 |
05-12-2013 | 1234 | 0002 | 80 |
06-12-2013 | 1234 | 0002 | 30 |
From the above data, I need to check that for Particular Plant and Material, how many orders are placed as per order date.
Thanks in Advance.............
Mine is slightly different from Gysbert Wassenaar's. You can choose which one works better for you:
Table1:
LOAD *, [Material No] & '|' & [Plant] as Key INLINE [
Material No, Plant, ATP Qty
28112, A016, 500
1234, A017, 100
];
Table2:
LOAD *, [Material No] & '|' & [Plant] as Key INLINE [
Order Date, Material No, Plant, Ordered Qty
2-11-2013, 28112, A016, 300
22-11-2013, 28112, A016, 200
24-11-2013, 28112, A016, 300
05-12-2013, 1234, A017, 80
06-12-2013, 1234, A017, 30
];
Output:
LOAD date#([Order Date], 'D-MM-YYYY') as [Order Date],
[Material No],
[Plant],
if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1')) as [ATP Qty],
[Ordered Qty],
if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty] as [Cumulative Qty],
if(if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty]<0, 'Invalid', 'OK') as [Status]
RESIDENT Table2
ORDER BY [Plant], [Material No], [Order Date];
DROP TABLES Table1, Table2;
See attached qvw
Mine is slightly different from Gysbert Wassenaar's. You can choose which one works better for you:
Table1:
LOAD *, [Material No] & '|' & [Plant] as Key INLINE [
Material No, Plant, ATP Qty
28112, A016, 500
1234, A017, 100
];
Table2:
LOAD *, [Material No] & '|' & [Plant] as Key INLINE [
Order Date, Material No, Plant, Ordered Qty
2-11-2013, 28112, A016, 300
22-11-2013, 28112, A016, 200
24-11-2013, 28112, A016, 300
05-12-2013, 1234, A017, 80
06-12-2013, 1234, A017, 30
];
Output:
LOAD date#([Order Date], 'D-MM-YYYY') as [Order Date],
[Material No],
[Plant],
if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1')) as [ATP Qty],
[Ordered Qty],
if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty] as [Cumulative Qty],
if(if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty]<0, 'Invalid', 'OK') as [Status]
RESIDENT Table2
ORDER BY [Plant], [Material No], [Order Date];
DROP TABLES Table1, Table2;
Thanks Gysbert and Nicole
It works Seamlessly...........
What would the original data look like for that? Because I'm not seeing how you would get that from the data in your original post...
HI Nicole,
PFA,
How about the attached?
Hi Nicole,
One more problem..
PFA,...
Hi Community,
anyone can help me..............
Is this what you mean?