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: 
thomasreynaert
Contributor III
Contributor III

QV Exclusion Operator in Set Expression

Hello community

I have been breaking my head on an exclusion done in a set expression. In my example this is my expression that I use:

min({$<Done={'no'},$(vDuty),$(vWorker)>-<Done={'no'},$(vExclDuty),$(vCustomer),$(vWorker)>}CreationDate)

The desired result would be the oldest unfinished task for a particular set of workers. However what the exclusion statement should achieve, is that it ignores certain tasks for certain customers.

Now attached you will notice that when starting from 1 table, the expression works perfect. However as in my real app, where I stumbled upon this problem, when there are several tables involved - QV will behave differently.

Any ideas on why this is? And more importantly, is there a way to fix this?

6 Replies
sunny_talwar

So, Min Excl is working fine, but Min Excl split file doesn't? What is the output you expect to see for Min Excl split?

thomasreynaert
Contributor III
Contributor III
Author

Date.JPG

Hi Sunny

I would expect the same output as for the app where everything is contained in 1 table.

For example CAB has 2 open tasks 'cook'. One of these is for a customer that is in the exclusion on 17/01/2018, the other is created on 28/02/2018. In my first app the last formula correctly calculates that the oldest open task is 28/02/2018. In the second app I made a second 'Workers' table where I assigned the name Alberto to 'CAB'. Here the formula gives me the overall minimum date.

Date2.JPG

sunny_talwar

Second part you are excluding also have a condition on Customer... but for this specific row, Customer is null... so that is why I think it is not getting excluded....

Capture.PNG

thomasreynaert
Contributor III
Contributor III
Author

Correct, and what you show here is coming out of the app where everything is in 1 table. It is exactly what I want, the row where there is no customer should be included, so it calculates the date.


The other row contains a customer that should be excluded, so here the minimum will not be visualized, because the set is empty. However on total level it does again give 28/02/2018.

sunny_talwar

I am not quite sure I understand why it won't be visualized? Would you be able to elaborate?

thomasreynaert
Contributor III
Contributor III
Author

Well suppose we would change the expression from minimum to a count and split the 2 blocks of set expression:


min({$<Done={'no'},$(vDuty),$(vWorker)>-<Done={'no'},$(vExclDuty),$(vCustomer),$(vWorker)>}CreationDate)


count({$<Done={'no'},$(vDuty),$(vWorker)>})-Count{$<Done={'no'},$(vExclDuty),$(vCustomer),$(vWorker)>}CreationDate)


If we would do that the results would be:

-1 first row

-0 second row

-1 on total

In our second row QV can not calculate a minimum because there are no records that comply to the set expression. Maybe visualize is bad terminology that I used, it just would give me a NULL value.