Finding next row with quantity from previous row

[JoannaM]

Hi all

I have a table:

Operation	Loc	Quantitiy	NextLoc
10	RM	3500	RM
20	WE	3400	WE
30	RM	3300	RM
40	PA	3200	PA
50	QQ	3100	QQ
60	MP		MP
70	SP		SP

I need this:

Operation	Loc	Quantitiy	NextLoc	NextQuantity
10	RM	3500
20	WE	3400
30	RM	3300
40	PA	3200
50	QQ	3100
60	MP		MP	3100
70	SP

Dataload:

Temp_00:
LOAD * INLINE [
Operation,Loc, Quantitiy
10,RM,3500
20,WE,3400
30,RM,3300
40,PA,3200
50 ,QQ,3100
60,MP
70,SP
];

Temp_10:
noconcatenate
LOAD rowNo() as OP,* resident Temp_00 order by Operation;
Drop Table Temp_00;

Temp_20:
noconcatenate
LOAD Operation,FirstSortedValue(Loc,OP) as NextLoc Resident Temp_10 Group By Operation;

join load * resident Temp_10 order by Operation;
drop table Temp_10 ;

How can I find the next row and take the quantity with me from the row I completed?

Thank you for your help.

Best regards,

Joanna

[albertovarela]

Hi @JoannaM ,  Try this out. It will get you started with what you are trying to achieve. 

Temp_00:
LOAD rowNo() as OP,* INLINE [
Operation,Loc, Quantity
10,RM,3500
20,WE,3400
30,RM,3300
40,PA,3200
50,QQ,3100
60,MP
70,SP
];

Temp_10:
LOAD
Operation,
Loc,
Quantity,
Peek(Quantity,-1) as PrevQty,
If(alt(Peek(Quantity,-1),0)=0
,Peek(PrevQty,-1)
,Peek(Quantity,-1)
) as NextQuantity,
Loc as NextLoc
Resident
Temp_00
Order By
OP;

DROP Table Temp_00;
DROP Field PrevQty;

[Saravanan_Desingh]

Can u explain more on your requirement?

What about SP? So, u want to carry the last Quantity 3100 to MP? 

[JoannaM]

Hi there

Basically, what I need to do is to show the next unfinished operation (in my example MP) and only the next operation but carry the quantity from the previous operation (my example QQ).

I need a row showing me MP with a quantity of 3100.

I hope this helps to explain a bit more of my needs.

BR, Joanna

[albertovarela · Accepted solution]

There you go.

Temp_00:
LOAD rowNo() as OP,* INLINE [
Operation,Loc, Quantity
10,RM,3500
20,WE,3400
30,RM,3300
40,PA,3200
50,QQ,3100
60,MP
70,SP
];

Temp_10:
LOAD
*,
If(NextQuantity > 0
,Loc
,''
) as NextLoc
;
LOAD
Operation,
Loc,
Quantity,
If(Quantity > 0 //eval if finished operation
,'' //change this as you see fit
,Previous(Quantity)
) as NextQuantity
Resident
Temp_00
Order By
OP;

[JoannaM]

Hi @albertovarela 

Thank you so much! That's what I need!

Br, Joanna