Need Help..

[#1]

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.............

[Gysbert_Wassenaar]

See attached qvw

[Nicole-Smith · Accepted solution]

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;

[#4]

Thanks Gysbert and Nicole

It works Seamlessly...........

[Nicole-Smith]

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...

[#6]

HI Nicole,

PFA,

[Nicole-Smith]

How about the attached?

[#8]

Hi Nicole,

One more problem..

PFA,...

[#9]

Hi Community,

anyone can help me..............

[Nicole-Smith]

Is this what you mean?