Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

3 Replies
daveamz
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

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

cwolf
Creator III
Creator III

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