Script assistance

[david_ze]

hi all,

I'll appreciate your assistance on the issue I have as described below:

I have two tables:
Table A contains import data:

Warehouse Product   Warehouse name  Quantity
1                    278                   A                    10
2                    278                   B                    30
3                    278                   C                    25
4                    278                   D                    50
5                    278                   E                   10

Table B contains product used:

Workorder Product  Quantity used
1            278              8
2            278             22
3            278             10

I would like for each Work Order to tag the Warehouse based on the inventory left on each Warehouse.

For example for the 1st work order I will use 8 items from Warehouse A, for work order 2 I will use the 2 items left on Warehouse A and the rest 20 will be from Warehouse B and, for work-order 3 will use the 10 items left in Warehouse 2.

Any idea how to do this in the load script ?

Thanks in advanced.
David

[swuehl · Accepted solution]

David,

I think you could try an interval match approach.

You need to create intervals for your Warehouse quantities, (1 to 10 assigned to A, 11 to 40 assigned to B etc.) and an atomic CurrentQuantUsed for your Workorders.

Then matching your CurrentQuantUsed into the intervals is straight forward.

TmpWarehouse:

LOAD * INLINE [

Warehouse, Product,   Warehouse name , Quantity

1,                    278,                   A,                    10

2,                    278,                   B,                    30

3,                    278,                   C,                    25

4,                    278,                   D,                    50

5,                    278,                   E ,                  10

];

Warehouse:

LOAD Warehouse, Product, [Warehouse name], Quantity, rangemax(1, peek(QuantityEnd)+1) as QuantityStart, rangesum(peek(QuantityEnd),Quantity) as QuantityEnd Resident TmpWarehouse order by Warehouse;

drop Table TmpWarehouse;

TmpOrders:

LOAD * INLINE [

Workorder, Product,  Quantity used

1,            278,              8

2,            278,             22

3,            278,             10

];

Orders:

LOAD *, Rowno() as CurrentQuantUsed resident TmpOrders while iterno() <= [Quantity used] order by Workorder;

drop table TmpOrders;

inner join IntervalMatch (CurrentQuantUsed) LOAD QuantityStart, QuantityEnd Resident Warehouse;

In the frontend, you just need to create a straight table chart with dimensions Workorder and Warehouse and as expression

=count(CurrentQuantUsed)

If you want to create this table also in the script, just add something like:

left join load * resident Warehouse;

TEST:

LOAD Workorder as WO, Warehouse as WH, count(CurrentQuantUsed) Resident Orders group by Workorder, Warehouse;

Regards,

Stefan

[david_ze]

Stefan, Thank you very much for the correct answer.
David.

[david_ze]

Hi Stefan,
One more question re this post:
Can you please advise how to do within the script you've send me if I would like to manage the Quantities based on the product.
Foe example I have quantity in the warehouse for Product 278 and also for 279 (can be also in the same warehouse) and also the product useed will be for product 278 and 279.

Can you please advice how should we do this in your script?

Thanks again for you help.

David

[swuehl]

David,

you can use the extended version of Intervalmatch for that, check the HELP for more details.

It could look like:

TmpWarehouse:

LOAD * INLINE [

Warehouse, Product,   Warehouse name , Quantity

1,                    278,                   A,                    10

2,                    278,                   B,                    30

3,                    278,                   C,                    25

4,                    278,                   D,                    50

5,                    278,                   E ,                  10

1,                    279,                   A,                    10

2,                    279,                   B,                    25

3,                    279,                   C,                    4

4,                    279,                   D,                    4

5,                    279,                   E ,                  10

];

Warehouse:

LOAD Warehouse, Product, [Warehouse name], Quantity,

rangemax(1, if(peek(Product)=Product,peek(QuantityEnd)+1)) as QuantityStart,

rangesum(if(peek(Product)=Product,peek(QuantityEnd)),Quantity) as QuantityEnd

Resident TmpWarehouse order by Product, Warehouse;

drop Table TmpWarehouse;

TmpOrders:

LOAD * INLINE [

Workorder, Product,  Quantity used

1,            278,              8

2,            278,             22

3,            278,             10

4,            279,              8

5,            279,             15

6,            279,             10

7,                                279,                                        5

8,            278,                                        6

9,                  279,                                        5

];

Orders:

LOAD *,

autonumber(RowNo(), Product) as CurrentQuantUsed

resident TmpOrders while iterno() <= [Quantity used] order by Product, Workorder;

drop table TmpOrders;

inner join IntervalMatch (CurrentQuantUsed, Product) LOAD QuantityStart, QuantityEnd, Product Resident Warehouse;

Regards,

Stefan