Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Henric_Cronström

 

In the Qlik engine, the logic of the selections is always an OR between selections in the same field, and an AND between selections in different fields. Selecting e.g. two products and one customer is very much like the WHERE clause in the following SELECT statement:

     SELECTWHERE (Product='Cap' OR Product ='Tracksuit') AND Customer='ACME' ;

Under some special circumstances, you can however use something called AND-mode. Read more about it in Jennell’s excellent blog post: AND-Mode. With AND-mode you can select two different products and find the customers that bought both.

Image2.png

 

However, the AND-mode logic is quite different from a standard AND operator in a WHERE clause: And it does not work at all the same way as OR-logic. There are theoretical implications that do not exist for OR logic.

For example: If you select two products and demand an OR between them, the possible values of all other fields are immediately determined: Any field value implied by either of the products is marked as “possible”.

But if you instead demand an AND between them, it is not clear what you mean: Do you mean “Customers” that have bought both products, or do you mean “Months” when both products have been sold? Or do you mean “Countries” where both products have been sold? Just specifying the two products is not enough to determine a result. You also need to specify the field that the AND-mode refers to.

The example shows that the AND-mode demands an intermediate iterator: The AND-mode always infers a second field for which the AND-logic is relevant. This is a theoretical problem that has nothing to do with how the logic is implemented in the software.

Let’s look at SQL: In a standard SELECT statement, the conditions on either side of the AND operator almost always concern two different fields. It would not make sense to demand

     SELECTWHERE Product='Cap' AND Product ='Tracksuit' ;

since there are no individual records that fulfill that requirement: "Product" can only have one value at the time. But this is exactly the type of requirement that you have in AND-mode - but operating on a group of records instead of on a single record.

If you would implement something similar to AND-mode in SQL, you would need to join a table with a copy of itself. The following will pick out customers that have bought both a Cap and a Tracksuit:

     SELECT DISTINCT Customer FROM Orders AS Orders1

     INNER JOIN Orders AS Orders2 ON Orders1.Customer=Orders2.Customer

     WHERE Orders1.Product='Cap' AND Orders2.Product='Tracksuit'

Again, an intermediate iterator is needed: Here it is "Customer" - the field used to join the two tables.

In QlikView we have chosen to solve this problem by demanding a two-column table for AND-mode, where the first column defines the iterator (e.g. Customer), and the second is the field where the user makes the AND selection (e.g. Product).

So, the two-column table is not just an arbitrary limitation; it is instead a framework implied by the theoretical problem.

HIC

19 Comments
datanibbler
Champion
Champion

Hi Henric,

I think I might not be sufficiently advanced in this grade of theorism:

=> To me it is not clear inhowfar it is not clear what is meant by selecting 2 values (say, products)
      (I could just keep Ctrl pressed to select several values)

=> I'd assume that there is a 1:n_relation between both

      - customers and products

       - months and products

=> If that is the case, I'd assume that QlikView would just show me all

- customers

- months

which are compatible with my selection? I don't see the need for the iterator, and, actually, I don't see the need for the entire "AND-mode"?

<=> Now, that is not at all compatible with your blog - so I guess I am wrong somehow
       => Please clarify this for me if you will.

Thanks a lot!

Best regards,

DataNibbler

0 Likes
4,086 Views
Henric_Cronström

In a standard situation, if you just select two values (ctrl-click) then QlikView will show all customers and months compatible with your selection, i.e. all field values pertaining to the first or the second selected value. Hence, this is an OR-selection.

However, AND-mode will interpret your selection differently, and return a much smaller result set.

Example: If you OR-select 'EU' and 'NATO', all five countries will be possible.

AND mode 3.png

But if you AND-select 'EU' and 'NATO', you will get a very different result:

AND mode 4.png

Read Jennell's blog post, and you'll understand better,

HIC

4,086 Views
datanibbler
Champion
Champion

Hi Henric,

[starting from the end]

I have read that post, you referenced it in your original blog-post - but there I was referred back here, which is actually no wonder because your posts are quite similar.

Thank you for clarifying!

Now I understand better.

Best regards,

DataNibbler

0 Likes
4,086 Views
robert99
Specialist III
Specialist III

Thanks. I was having problem with this yesterday (and put it off until today)

What is the best way to write not equal. I assume it is


SELECTWHERE (Product<> 'Cap' OR Product <> 'Tracksuit') AND Customer <> 'ACME' ;

0 Likes
4,086 Views
Henric_Cronström

Both the following syntaxes should work in both a SELECT and a Load:

   Product<> 'Cap'

   not Product= 'Cap'


In addition, you can use the following in a Load:

   not Match (Product, 'Cap')


And in the QlikView UI, you can right-click and choose 'Select Excluded'


HIC

0 Likes
4,086 Views
rwunderlich
Luminary Alumni
Luminary Alumni

I think your choice of operator may be incorrect

SELECTWHERE (Product<> 'Cap' OR Product <> 'Tracksuit')

That would be true for every row,


I think you want

SELECTWHERE (Product<> 'Cap' AND Product <> 'Tracksuit')

0 Likes
4,086 Views
robert99
Specialist III
Specialist III

Rob

Thanks

Yes this is the problem I had

SELECTWHERE (Product<> 'Cap' OR Product <> 'Tracksuit')

did NOT exclude either Cap Or Tracksuit

But

SELECTWHERE (Product<> 'Cap' AND Product <> 'Tracksuit')

excluded both Cap and Tracksuit (which is what I wanted)

This makes no sense to me as I assumed or would be correct. But and works and or doesn't

I will think about this on the way home


EDIT OK I understand now

And  SELECTWHERE (Product<> 'Cap' AND Product <> 'Tracksuit') AND Customer <> 'ACME' ;




0 Likes
2,726 Views
michael_gardner
Creator III
Creator III

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
  • SELECT....WHERE Product NOT IN ('Cap','Tracksuit');

0 Likes
2,726 Views
robert99
Specialist III
Specialist III

Thanks Michael (and Henric)

I will use WHERE NOT MATCH(Product,'Cap','Tracksuit') AND NOT MATCH(Customer,'Acme'); for a load statement

And for select

where not Product = 'Cap' ;  (for one selection)

or

WHERE Product NOT IN ('Cap','Tracksuit'); (for more than one)

2,726 Views
michael_gardner
Creator III
Creator III

Hey RJ,

I totally agree with you that it is confusing that Where Product = 'Cap' or Product ='Tracksuit' would include only those two records in your results but Where Product <> 'Cap' or Product <> 'Tracksuit' does not exclude the two records and vice versa Where Product = 'Cap' and Product = 'Tracksuit' would exclude all records instead of just including the two specified.   That is why I prefer the IN and Match snytax.

You can go to SQL AND & OR Operators and play around with AND & OR operators to get more comfortable.

2,726 Views