Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a question about peek/previous. I have the following problem in my table:
Order | Operation | Date | RecordType |
---|---|---|---|
100 | 1 | 01-01-2014 | Start |
100 | 2 | 02-01-2014 | Middle |
100 | 3 | 03-01-2014 | Middle |
100 | 4 | 04-01-2014 | End |
100 | 5 | 10-01-2014 | Middle |
100 | 6 | 15-01-2014 | Middle |
101 | 1 | 20-01-2014 | Start |
101 | 2 | 22-01-2014 | Middle |
etc. |
For example, order 100 is completed at 04-01-2014, which is a given from a different dataset. Every operation after the enddate is just an administrative thing, where the operation is marked as complete.
I want the order to end after operation number 4. I use the following code, which works well if there is only one Middle record after the end:
if(Previous([ProductionOrder]) = [ProductionOrder] AND Previous([RecordType]) = 'end', 'Exclude', 'Include') | as [Exclusion] |
But now only Operation 5 is excluded. How can I exclude operation 6 as well?
Hi Willem,
Use an additional condition:
If(Previous([ProductionOrder]) = [ProductionOrder] AND (Previous([RecordType]) = 'end' OR Peek([Exclusion]) = 'Exclude'), 'Exclude', 'Include') as [Exclusion]
Peek will check if previous row was excluded (as Previous([ProductionOrder]) = [ProductionOrder]).
Regards,
David
You can Create a flag as start=1, middle=0 and end=-1. Now if the rolling sum of this flag=1 or RecordType= End then Include else Exclude. Check the attached QVW for the solution.
Cheers
Hi,
Left Join (OrderTable)
LOAD
Order,
Operation as MaxOp
Resident OrderTable
Where RecordType='End';
OrderTableNew:
LOAD
*,
if(IsNull(MaxOp) or Operation<=MaxOp,'Include','Exclude') as Exclusion
Resident OrderTable;
DROP Table OrderTable;
Regards
Christian