Committing quantities in sequential order

[Anonymous]

Dear Ladies and Gents,

I have been asked to provide a report and I have no idea where to start on this one...

I have put a simplified data framework to explain this logically...

Basically I have purchase orders for different products for different quantities. The same product may be ordered in more than one purchase order as illustrated below:

Then I have Sales Orders in Date order for the same products as per below:

What I need the Qlikview report to achieve is to somehow match the quantities on Sales Orders to the quantities on Purchase orders and allocate the Purchase Order quantities to the Sales Order quantities in Date Order. This is to ensure that quantities purchased are allocated to sales orders on a first come first serve basis for that product.

Here is the end result ( i have included comments, but really all we need is a link from PO to SO)

I would see this as a pivot table with PO and PO product quantities on the left, and allocated SO quantities to the right.

I have added some comments in the right hand column below, but qty allocated / qty fulfilled (yes/no) / qty remaining could be additional columns

At present, I have no idea where to start to achieve this... any help would be appreciated...

I'm attaching an excel sheet with this sample data in excel format in separate tabs...

Thanking everybodyin advance...

Thanks..

Fab

[Kushal_Chawda]

Can you also put the actual number or output needed ?

[Anonymous]

Hi Kushal

Thanks for your interest...

I have focused the output to just the Product QTY being allocated (to the Sales Order) from that particular Purchase Order line.

If the qlikview calculates the S/O number that is next in line to be allocated a quantity and also the qty allocated - that would be sufficient for my requirements...

I have re-attached the results with the sheet below on the "results" tab...

Thanks again for your interest - let me know if anything is not clear..

Fab