Using Loop for multiple Load statement

[#1]

Hello,

Kindly help me in solving the below issue.

Test:

Plant     Material     Tot_Qty

ABC     1001          500

XYZ     1002          1000

Order

OrderNo    Plant     Material     Date           Qty_2

1               ABC        1001      1/1/2013        100

2               ABC        1001      2/10/2013      200

3               ABC        1001     15/12/2013     200

From the above data, I need to check that for Particular Plant and Material, how many orders are placed as per order date.

For Eg,

OrderNo : 1, Qty_2 : 100.

Subtract Qty_2 from Tot_Qty and loop it till the Tot_Qty becomes 0.

Thanks

Sumeet

[Nagaraju_KCS]

Hi Community,

I have same problem like this

please give me anyone solution ASAP,

---Raju

[jagan]

Hi,

Can you clarify what should be your final output with example.

Regards,

Jagan.

[vikasmahajan]

I think you should try RangeSum(Above(Sum(Qty), 0, RowNo()))

Please find  attachment of demo which can help you.

Vikas

[Nagaraju_KCS]

Hi Jagan,

Output would be

Test:

Plant     Material     Tot_Qty

ABC     1001          500

XYZ     1002          1000

Order

OrderNo    Plant     Material     Date           Qty_2

1               ABC        1001      1/1/2013        100

2               ABC        1001      2/10/2013      200

3               ABC        1001     15/12/2013     200

O/P:

Row1:

OrderNo    Plant     Material     Date           Qty(Tot_Qty-Qty_2)

1               ABC        1001      1/1/2013         400

Suppose Tot_Qty=500

                 Qty_2=100

(Tot_Qty-Qty_2)= 500-100

Now, 400 would be Tot_Qty for next OrderNo

400-200

---Raj

[jagan]

Hi,

Please try this script

Temp:

LOAD * Inline [

Plant,     Material,     Tot_Qty

ABC,     1001,          500

XYZ,     1002,          1000];

Left Join

LOAD *

Inline [

OrderNo,    Plant,     Material,     Date,           Qty_2

1,               ABC,        1001,      1/1/2013,        100

2,               ABC,        1001,      2/10/2013,      200

3,               ABC,        1001,     15/12/2013,     200

];

Data:

LOAD

*,

If(Previous(Plant) = Plant and Previous(Material) = Material , Peek('TempTotal') - Qty_2, Tot_Qty - Qty_2)  AS TempTotal

RESIDENT Temp

ORDER BY Plant, Material, OrderNo;

DROP TABLE Temp;

Regards,

Jagan.

[Nagaraju_KCS]

Hi Jagan,

The above Solution is not working for some values.

PFB snapshot of the data.

Order Date	Material No	Plant	ATP Qty	Ordered	Cumulative Qty	Status
2/11/2013	28112	A016	55050	100	54950	OK
22-11-2013	28112	A016	55050	100	54850	OK
26-11-2013	28112	A016	55050	100	54750	OK
26-11-2013	28112	A016	55050	200	54550	OK

Order Date	Material No	Plant	ATP Qty	Ordered	Cumulative Qty	Status
2/11/2013	28112	A016	100	80	20	OK
22-11-2013	28112	A016	100	50	-30	Invalid

The solution provided by you is giving the attached output.

[maxgro]

Hi

please see attachment, result in image

Regards

[jagan]

Hi,

Can you attach the file and data which you are working, I think your script has issue.  Did you ordered the resultset

ORDER BY Plant, Material, OrderNo

Regards,

Jagan.

[#10]

Hi Massimo/Jagan,

The Result set for the data should be :

Order Date	Material No	Plant	ATP  Qty	Ordered	CumulativeQty	Status
2/11/2013	28112	A016	55050	100	54950	OK
22-11-2013	28112	A016	55050	100	54850	OK
26-11-2013	28112	A016	55050	100	54750	OK
26-11-2013	28112	A016	55050	200	54550	OK

For 1st Row, ATP Qty - Ordered (55050-100 = 54950)

For 2ns Row, ATP Qty  should be the result of 1st Row. ie. (54950-100 = 54850) and so on.

Kinldy help as how to get these type of result in a loop. Also the moment we get any negative result set, loop should be terminated with the apprpriate status.

Regards

Sumeet