Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 3100 | ||
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 | 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
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;
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;
Can u explain more on your requirement?
What about SP? So, u want to carry the last Quantity 3100 to MP?
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
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;