19 Replies Latest reply: Apr 7, 2010 4:16 PM by Michael Solomovich RSS

    OR Operator between List Boxes

    Saravana Prabhu Paramaramaraj

      Is it possible to implement OR between two list box?

      Say for example:

      List Box1: 1 2 3

      List Box2: A B C

      and let Table Box which shows the records of corresponding selections made in List Box1 and List Box2.

      By default, List Boxs narrow down the result using AND operation.

      This is where my question arises.

      Is it possible to implement OR between two list box?

      Advance Thanks

        • OR Operator between List Boxes
          Piet_Orye

          Option:

           

          Since the fields are linked, you cannot do a simple direct OR.

          But this is a possible workaround.

          Assume, we have a table with 2 fields, we import it

           

           

          LOAD FieldA,

          FieldB

          FROM

          C:\Data\QV_2.xlsx

          (ooxml, embedded labels, table is Blad1);

           

          Now we need a fully independent list of distinct FieldA values and of distinct FieldB values; we'll select from those:

          For FieldB:

           

          LOAD distinct FieldB AS FB

          FROM

          C:\Data\QV_2.xlsx

          (ooxml, embedded labels, table is Blad1);

           

          For FieldA:

           

          LOAD distinct FieldA AS FA

          FROM

          C:\Data\QV_2.xlsx

          (ooxml, embedded labels, table is Blad1);



           

          Build a list box from FA // you can name the listbox FieldA

           

          Build a list box from FB // ...

           

          Build a chart (not a table):

          select "straight table" type

          add dimensions

          =if(isnull(FA) or FA=FieldA or isnull(FB) or FieldB=FB,FieldA) // enter "FieldA" as the name

          =if(isnull(FA) or FA=FieldA or isnull(FB) or FieldB=FB,FieldB) // enter "FieldB" as the name

          PS: you need the "isnull(FA)" part for if nothing is selected in the list box for FA; i.e. all values are OK

          the FA=FieldA part for actual selections; only selected values are OK; etc

          add an expression - you need one... e.g

          'OR' // name it Dummy or such, you can set the column width to 0

          That's it.

           

          Greetings,

          Piet



            • OR Operator between List Boxes
              Saravana Prabhu Paramaramaraj

               

               

               

               

               





               

               

               

               

               



               

               

               

               

               

               

               





               

               

               

               

               

               

               

               

               



               

               

              Hi Piet,

              Thanks..

              Load Script :

              ----------------

              Directory;

              LOAD

               

               

              #,

              ID

               

              ,

              Name

               

              ,

              [How Recruited?]

               

              ,

              Designation

               

              ,

              DOB

               

              ,

              Horizontal

               

              ,

              Onsite

               

              ,

              Vnet

               

              ,

              [Contact #]

               

              ,

              Allocated

               

              ,

              Billed

               

              ,

              [Billing %]

               

              ,

              [Client Email],

              [Personal Email]

               

              ,

              [Emp Start date]

               

              ,

              Location

              FROM

              [DW Model From Sar1.xlsx]

              (

              ooxml, embedded labels, table is Associate);

               

               

              DesignationTab:





               

              Load

               

               

              Distinct

               

               

               

               

               

              Designation

               

               

              as

              Designation1

               

              from

               

               



               

               

               

               



               

               

               

               

              [DW Model From Sar1.xlsx]

              (

               

              ooxml, embedded labels, table is Associate)

              ;

               

               

               

               



               

               

              LocationTab:

              Load

               

               

              Distinct

               

               

               

               

               

              Location

               

              as



              Location1

               

               

               

              from

              [DW Model From Sar1.xlsx]

              (

               

              ooxml, embedded labels, table is Associate)

              ;

              -----------------------------------------------------------------------------------------------------------------

              Straight table (Calculated Dimensions) :

              -------------------------------------------------------



               

              =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Location)

               

              =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Designation)

              ---------------------------------------------------------------------------------------------------------------------

              And also I've added an expression to straight table sum(0)

               

              But still it is not working. Can you please help?





              • OR Operator between List Boxes
                Saravana Prabhu Paramaramaraj

                Sorry for the previous post.


                Hi Piet,

                Thanks..

                Load Script :

                ----------------

                Directory;

                LOAD #,
                ID,
                Name,
                [How Recruited?],
                Designation,
                DOB,
                Horizontal,
                Onsite,
                Vnet,
                [Contact #],
                Allocated,
                Billed,
                [Billing %],
                [Client Email],
                [Personal Email],
                [Emp Start date],
                Location
                FROM
                [DW Model From Sar1.xlsx]
                (ooxml, embedded labels, table is Associate);


                DesignationTab:
                Load Distinct
                Designation
                as
                Designation1
                from
                [DW Model From Sar1.xlsx]
                (ooxml, embedded labels, table is Associate);



                LocationTab:
                Load Distinct
                Location
                as
                Location1
                from
                [DW Model From Sar1.xlsx]
                (ooxml, embedded labels, table is Associate);

                -----------------------------------------------------------------------------------------------------------------

                Straight table (Calculated Dimensions) :

                -------------------------------------------------------

                 

                =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Location)


                =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Designation)

                ---------------------------------------------------------------------------------------------------------------------

                And also I've added an expression to straight table sum(0)

                 

                But still it is not working, data is not getting loaded in the straight table. Can you please help?

                 

                  • OR Operator between List Boxes
                    Piet_Orye

                    Saravana,

                    works like that, except for:

                    1) Sum(0) will return 0, and not show anything, unless you remove the "suppress zero values" from the "presentation" tab
                    'OK' or 1 or sum(1) ... will work without that

                    2) Make sure that you have selected the 'straight table' and not a line chart or so

                    Greetings,

                    Piet

                     

                      • OR Operator between List Boxes
                        Saravana Prabhu Paramaramaraj

                        Piet,

                        Yes as you said there is problem with my sum(0) expression, now data is getting loaded by removing the expression and unchecking suppress zero values.

                        But even now I'm facing a problem with the following expressions

                        =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Location)

                        =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Designation)

                        It is loading all possible combinations for each row.

                        I mean, say record 1 be,

                        Name Designation Location

                        Peter Manager Chennai

                        for this record,

                        Peter Manger Chennai

                        Peter - Chennai

                        Peter Manager -

                        Peter - -

                        these many records are getting loaded

                        Thanks.

                  • OR Operator between List Boxes
                    Piet_Orye

                     


                    Well you are correct, doesn't work for multiples.
                    Solution:
                    Back to the initial design, but with an extra calculated dimension for Name:
                    Dimensions:
                    -> for location & designation as before
                    -> for name: =if(isnull(Location1) or Location1=Location or isnull(Designation1) or Designation=Designation1,Name)
                    Expression:
                    -> Back to 'OK', or the sum you had

                    Small remark, it now shows an all Null/Null/Null/'OK' line in the list,
                    if you really do not want that, use the following expression instead:
                    Expression:
                    if(isnull(Designation) and isnull(Location) and isnull(Name),Null,'OR')
                    And re-enable "Suppress zero values" on the "presentation tab" !
                    Example result below:
                    Piet


                      • OR Operator between List Boxes
                        Saravana Prabhu Paramaramaraj

                        Hi Piet,

                        It is working, if I have either selected both the list box or not, but for selection in one list box its not working.

                        Piet can you please explain me what the following expressions do:

                        (1) isnull(Location1)

                        (2) Location1=Location

                        I hope that I'm close to fix the problem.

                        Thanks for your continuos support.

                          • OR Operator between List Boxes
                            Piet_Orye

                            (See file in attach for three options)

                            I think that we have had a misunderstanding about the end-result.

                            Until now I understood:

                            if Location1 is selected OR if Designation1 is selected, then show the record

                            where "nothing clicked in a list" is interpreted as nothing de-selected, so "everything selected"

                            I assume that you want that to mean "nothing selected"

                            Possible via this formula:

                             


                            =if(
                            if(isnull(GetFieldSelections(Designation1)),'<no match',Designation1)=Designation OR
                            if(isnull(GetFieldSelections(Location1)),'<no match',Location1)=Location,
                            Location)


                            GetFieldSelections(...) is Null() if nothing selected, else a list of the selected items

                            So if nothing is select I compare Designation with string "<no match>" > to avoid a match; if something is selected, I compare Designation1 with Designation.

                            Greetings,

                            Piet

                              • OR Operator between List Boxes
                                Saravana Prabhu Paramaramaraj

                                 

                                Hi Piet,

                                Finally I got what was expected by me. This is the expression that lead.

                                = if(if(isnull(GetFieldSelections(Designation1)),'<no match',Designation1)=Designation OR if(isnull(GetFieldSelections(Location1)),'<no match',Location1)=Location,Location, if(getselectedcount(Designation1)=0 and getselectedcount(Location1)=0, if(Not(isnull(Location1)) and Not(Location1=Location) and Not(isnull(Designation1)) and Not(Designation=Designation1),Location)))

                                Piet can you please explain me what the following expressions do(from previous):

                                (1) isnull(Location1)

                                (2) Location1=Location

                                Without you this wouldn't be possible, thanks alot for your continuous support.



                                  • OR Operator between List Boxes
                                    Piet_Orye

                                    Well, I'm also discovering a lot of this as I go...

                                     

                                    (2) Location1=Location

                                    ~ if there is an intersection between group "selected Location1" and group "Location" ... add those Locations to the dimension set

                                    (1) Not sure that is required if all the setting of "show/hide zero values" is ok

                                     

                                    Maybe an option if you like to experiment:

                                    I was working on something else when an other perhaps more straightforward solution came to me:

                                    (don't know if you have a registered copy of QV, if so see attach, else)

                                     


                                    Again a straight table, but with the main fields simply selected as dimensions, here:
                                    // main fields are ProductType, Code, OrderID
                                    // the extra OR selection values are ProductTypeOR and Code OR
                                    dimension: ProductType
                                    dimension: Code
                                    calculated expression = using an assemble "like" expression between value & GetFieldSelections():
                                    if(

                                    if(getselectedcount(ProductTypeOR)=0,'<nomatch>',GetFieldSelections(ProductTypeOR,' ',9999))
                                    like ('*' & if(isnull(ProductType),'<no!match>',ProductType) & '*') OR
                                    if(getselectedcount(CodeOR)=0,'<nomatch>',GetFieldSelections(CodeOR,' ',9999))
                                    like ('*' & if(isnull(Code),'<no!match>',Code) & '*'),
                                    OrderID)
                                    // named as OrderID
                                    // suppress zero values in presentation

                                    Optional:
                                    Color codes for dimensions, eg, backgroup color for all ProductType selected in ProductType1:

                                    =if(
                                    if(getselectedcount(ProductTypeOR)=0,'<nomatch>',GetFieldSelections(ProductTypeOR,' ',9999)) like ('*' & if(isnull(ProductType),'<no!match>',ProductType) & '*'),
                                    rgb(180,220,255))

                                    Greetings,
                                    Piet


                                      • OR Operator between List Boxes
                                        John Witherspoon

                                        One way to handle an OR is obviously what has already been presented - create new fields with different names, then match those new fields back to the real fields using complicated expressions in the table.

                                        Another way to handle the OR is attached. Create an OR table and allow QlikView's default logic to do all the heavy lifting in the charts. I'm guessing that this approach would be significantly faster for large data sets, where you only have a few fields you want to do the OR on, and only a few values for those fields. However, it can eat up a lot of memory for that performance in some cases, so certainly isn't the solution in all cases. It's just an alternative to consider.

                                        As best I can tell from skimming the thread, it doesn't do exactly what you're asking for, such as it uses the default definition of no selections in a field, which is to assume that all values are allowed. But some adjustments from this might produce the results you want.

                                        Probably not worth it if you have a solution that you're happy with. But I wanted the alternative to be out there for other people looking for old threads on the subject.

                                          • OR Operator between List Boxes
                                            Saravana Prabhu Paramaramaraj

                                            Hi John,

                                            Can you make the example personal edition compatible?

                                             

                                            Regards,

                                            Prabhu

                                             

                                              • OR Operator between List Boxes
                                                John Witherspoon

                                                 


                                                Saravana Prabhu wrote:Can you make the example personal edition compatible?


                                                How do I make it personal edition compatible? I honestly don't know.

                                                In the mean time, I'll copy the script below. Make list boxes for A, B and C. Create a table box with ID and OtherField. You might also want a table box with everything from the Raw Data table so that you can see what the results should be as you make selections.

                                                [RawTable]:
                                                LOAD * INLINE [
                                                RawID, RawA, RawB, RawC, RawOtherField
                                                1,Y,N,N,A
                                                2,N,Y,N,B
                                                3,Y,N,Y,C
                                                4,Y,Y,Y,D
                                                5,Y,N,N,E
                                                6,N,N,N,F
                                                7,Y,Y,Y,G
                                                ];
                                                [MainTable]:
                                                NOCONCATENATE
                                                LOAD
                                                RawID as ID
                                                ,RawOtherField as OtherField
                                                RESIDENT [RawTable]
                                                ;
                                                [OrTable]:
                                                LOAD
                                                RawID as ID
                                                ,RawA as A
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTable])
                                                LOAD DISTINCT RawB as B
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTable])
                                                LOAD DISTINCT RawC as C
                                                RESIDENT [RawTable]
                                                ;
                                                [OrTableB]:
                                                NOCONCATENATE LOAD
                                                RawID as ID
                                                ,RawB as B
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTableB])
                                                LOAD DISTINCT RawA as A
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTableB])
                                                LOAD DISTINCT RawC as C
                                                RESIDENT [RawTable]
                                                ;
                                                [OrTableC]:
                                                NOCONCATENATE LOAD
                                                RawID as ID
                                                ,RawC as C
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTableC])
                                                LOAD DISTINCT RawA as A
                                                RESIDENT [RawTable]
                                                ;
                                                LEFT JOIN ([OrTableC])
                                                LOAD DISTINCT RawB as B
                                                RESIDENT [RawTable]
                                                ;
                                                JOIN ([OrTable])
                                                LOAD *
                                                RESIDENT [OrTableB]
                                                ;
                                                JOIN ([OrTable])
                                                LOAD *
                                                RESIDENT [OrTableC]
                                                ;
                                                DROP TABLES
                                                [OrTableB]
                                                ,[OrTableC]
                                                ;