11 Replies Latest reply: Jun 29, 2011 3:13 PM by Jonathan Love RSS

    AND not OR

    Jonathan Love

      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.

        • AND not OR
          Leonard Short

          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?

          • Re: AND not OR
            John Witherspoon

            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.

              • Re: AND not OR
                Jonathan Love

                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.

                  • Re: AND not OR
                    Nagaian Krishnamoorthy

                    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.

                      • Re: AND not OR
                        Jonathan Love

                        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

                        • Re: AND not OR
                          Jonathan Love

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

                          • Re: AND not OR
                            Jonathan Love

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

                              • Re: AND not OR
                                Nagaian Krishnamoorthy

                                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.

                                  • Re: AND not OR
                                    Jonathan Love

                                    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

                                    • Re: AND not OR
                                      Jonathan Love

                                      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