Filters in NPrinting – how hard can it be? (Part 2 – “OR Mode”)

1
Written by Lech Miszkiewicz

Intro

The inspiration for this post came recently where I attempted to answer this post on Qlik Community and to be honest everything you need to know is already answered there. It is true that sometimes it is hard to find the correct answers on Qlik Community just because subject may be inaccurate or too generic or simply unrelated like those “NPrinting issues” subjects, each talking about different “issue” (see picture below) – therefore I decided that I will do it all over again and cover this topic here in dedicated blog post.

Task

Today’s task is simple. We have a data set with 3 dimensions and 1 measure. From this data we want to filter out records which meet following criteria:

  • Dim1 = A (Light Blue)
  • or Dim2 = d (Pink)
  • or Dim3 = X (Purple)

NPrinting filters are working just like QlikView/Qlik Sense selections, so first question is: How would you apply selection to only show you those records with coloured background? You cannot apply selections on fields Dim1, Dim2 & Dim3 as such selection returns only intersected records.

Solution 1

There is no easy way to apply such filter. One option would be to create a flag during load script which you can use to filter out required records:

If(Dim1='A' or Dim2='d' or Dim3='X',1,0) as _OrFlag

Having flag like the one above may not always be possible as it would require all 3 fields to be in the same table during load for condition to be applied. It also requires changes to Qlik load script which is not always possible. It would be my preferable option though whenever possible as it is easy to implement in QlikView/Sense and in NPrinting, performs well and is the easiest one to troubleshoot in case there are some discrepancies. Below screenshot shows that this worked well.

Solution 2

Introduction of Advanced search filter in NPrinting opened a completely new ways of how we can filter data in NPrinting. The only thing we have to ensure is the granularity of data in the field we are going to use to apply filter on. If we take into account our example data, the granularity we require sits on Dim1 & Dim2 & Dim3 level. In the sample app I have created a field which combines those 3 fields together into TransId field. If our Qlik data model does not allow us to use those fields, we can still use other fields to create a unique key as long as they represent the same or more granular values. 

(Dim1 & '|' & Dim2 & '|' & Dim3) as TransId,

If we are not keen on creating a field with the required granularity we can always use some unique identifier which we may already have in your data model. Example taken from business may be “OrderLineId”, which can be a unique identifier of transaction line. I am not big fan of using such field for filtering purposes as it will slow down performance and consume lots of resources but in the scenarios where you cannot change your Qlik load script this would be your only available solution. In the provided example I have created dummy “OrderLineId” field too:

RowNo()	as OrderLineId,

Having both those fields (TransId and OrderLineId) created we can demonstrate that using advanced search option on either of them will allow us to filter data according to our needs. Syntax used in search filter is shown below and can be tested in QlikView or Qlik Sense first as shown on the below picture. With my approach I am interested in results where I have sum(Expression1) > 0 and the set analysis part is used to create an “Or mode” for my selection by using “+” sign between set modifiers. 

=sum({<Dim1={A}>+<Dim2={d}>+<Dim3={X}>}Expression1) > 0

Now when we confirmed that our filter is working ok in Qlik Sense we can go apply it in NPrinting as shown below:

If necessary, we could apply this filter to “OrderLineId” field and it would yield the same results.

Summary

People underestimate how much more flexibility we have knowing how to put to use advanced search filters properly. It is very powerful and often forgotten method. There are many variations we can do with it but I will not try to cover all of them in this post – let’s keep it short and simple.

I would like to encourage everyone to check other resources on that subject. Below are links I think are worth checking:

Link to Or Filter.qvf app.

1 Comment

Leave a comment