Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
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

Highlighted
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

Highlighted
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