topic Re: QV Exclusion Operator in Set Expression in QlikView

QV Exclusion Operator in Set Expression

thomasreynaert — Tue, 24 Apr 2018 12:23:45 GMT

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?

Re: QV Exclusion Operator in Set Expression

sunny_talwar — Tue, 24 Apr 2018 12:32:53 GMT

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?

Re: QV Exclusion Operator in Set Expression

thomasreynaert — Tue, 24 Apr 2018 13:06:25 GMT

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.

Re: QV Exclusion Operator in Set Expression

sunny_talwar — Tue, 24 Apr 2018 13:10:45 GMT

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....

Re: QV Exclusion Operator in Set Expression

thomasreynaert — Tue, 24 Apr 2018 13:17:04 GMT

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.

Re: QV Exclusion Operator in Set Expression

sunny_talwar — Tue, 24 Apr 2018 13:29:59 GMT

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

Re: QV Exclusion Operator in Set Expression

thomasreynaert — Tue, 24 Apr 2018 13:43:37 GMT

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.