Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Removing Results with Peek or Previous

Hello, I have a question about peek/previous. I have the following problem in my table:

OrderOperationDateRecordType
100101-01-2014Start
100202-01-2014Middle
100303-01-2014Middle
100404-01-2014End
100510-01-2014Middle
100615-01-2014Middle
101120-01-2014Start
101222-01-2014Middle
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?

Tags (2)
3 Replies
daveamz01
Contributor III

Re: Removing Results with Peek or Previous

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

utkarshgarg
Contributor II

Re: Removing Results with Peek or Previous

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

chrwolf64
Contributor III

Re: Removing Results with Peek or Previous

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