AND not OR

[Anonymous]

I apologize if this is a bit fundamental, but I can't seem to figure it out on my own.  I want to display records where two conditions are met.  For example show me all "customers" where "product_category" is "A" AND "B" - As opposed to the traditional "A" OR "B"  I deally I would like to implement it with a button action (A|B) that is AND not OR.

[#2]

You could use something like this:

count(if(product_category = A and product_category = B,customers))

another option would be to use a calculated dimension using an if statement like:

if(product_category = a and product_category = b, 1, null())  then select the 'suppress when value is null' option & it wont display any data for customers who dont meet the criteria.

Not sure what all you want to do with the button? Just apply a filter?

[johnw]

In the help index, look for "And-mode in List Boxes".  If you set things up a certain way, you can make a list box behave as if the selections are AND instead of OR.  As for your button, you'd go ahead and select both A and B, and they should then behave as AND since that's how that selection has been set up.  Attached is an example of an and-mode list box.

[Anonymous]

That is great to know John, thank you.  Unfortunately the data model does not meet the criteria for And-mode in List Boxes in this case - and its not something that I can change.

[nagaiank]

Using And-mode listbox is user-interactive. (Using a set analysis expression is not as user-interactive as a listbox.)

Instead of discarding this option with the thought that the data model cannot be changed, can we be creative to arrive at an extension of data model which will meet the requirements of And-mode listbox?

• the field must only exist in one logical table,
• the field must be the second column of no more than two columns, and
• the table must not contain any duplicate records, and
• the table must be loaded using a distinct qualifier. If the table is loaded using a Select statement, you must use a preceding Load distinct *.

I was thinking of creating a new logial table with te fields Customer and ProductCode field. If the Productcode field renamed differently than the field in your transaction table, Qlikview will not link the fields.

You can load these two fields from your transaction table, using 'load distinct ...' as required for and-mode listbox and have and-mode ProductCode selection for this field.

If you want to have further filtering using other selection(s), we need to reconsider the whole approach.

Not having the details of your data model, I am unable to think further on this issue.

Hope this helps to enhance your data model.

[Anonymous]

Thank you for your reply. The qvw resides on a server and I simply do not have access to the script in this case. I am going build a small local app and apply the and mode suggestion made by you and John. I will let you all know how it goes.

Thanks so much for the help.

Regards, Jonathan

[Anonymous]

Trying to build a very small app just to prove to myself that this can be done.  I believe i have met all the conditions set forth in the index, the field "Brand" only exist in one table, the field is the second of only two columns in that table, there are no duplicates, and I LOAD DISTINCT.  When I create my new list box and use the field "Brand" - I do not have the option to select "And mode"  Thoughts?  (Note - the FROM statement is actually pulling data from the same table in the datamart, not sure that should matter)

II_Customer:

LOAD

Ind_item_number,

Serial_number,

owner;

SQL SELECT *

FROM "...datamart..."

_____

II_Brand:

LOAD DISTINCT

Ind_item_number,

Brand;

SQL SELECT *

FROM "...datamart..."

[Anonymous]

Trying to build a very small app just to prove to myself that this can be done.  I believe i have met all the conditions set forth in the index, the field "Brand" only exist in one table, the field is the second of only two columns in that table, there are no duplicates, and I LOAD DISTINCT.  When I create my new list box and use the field "Brand" - I do not have the option to select "And mode"  Thoughts?  (Note - the FROM statement is actually pulling data from the same table in the datamart, not sure that should matter)

II_Customer:

LOAD

Ind_item_number,

Serial_number,

owner;

SQL SELECT *

FROM "...datamart..."

II_Brand:

LOAD DISTINCT

Ind_item_number,

Brand;

SQL SELECT *

FROM "...datamart..."

[nagaiank · Accepted solution]

I think your code meets the requirements of And-Mode listbox and so I think you will have the option to select And-Mode. If it is not the case, please send your application with sample data.

Alternatively I have attached an example of implementing the And-Mode listbox and see if it helps.

[Anonymous]

Got it!  When I looked at your app (And John's) I could see how your boxes worked, and that they had "And Mode" available - but I couldn't even create new boxes in either of your apps with And Mode..until I created a box and saved, then opened it back up and bingo - now I can edit the properties to enable And Mode in both of your apps and in my own!  Thanks guys!

Jonathan