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

OR Operator between List Boxes

Saravana Prabhu P

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 P

       

       

       

       

       





       

       

       

       

       



       

       

       

       

       

       

       





       

       

       

       

       

       

       

       

       



       

       

      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 P

      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 P

          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

            Maybe I misunderstand what you really want to do?

            See here a capture of what my method delivers:

            Straight table chart "OR'ed" -> shows 1 record based on Designation1=32 selection; + 2 records based on the Location1 selection. So either (OR) selection yields records.

            For reference I show the complete list below, so you can verify the result.

            Piet

          • OR Operator between List Boxes
            Piet_Orye

            Thought of something else!

            Make sure that you add "Name" and other fields as an Expression, and not as another Diemension!

            Example OR'ed is OK, the one on the right has Name as extra dimension and is wrong

             

            • OR Operator between List Boxes
              Saravana Prabhu P

              Hi Piet,

              Thanks for your efforts.

              In your previous post the records of the full list are unique according to designation1 and location1 this is where I'm facing problem.

              Say your full list contains another two records like :

              Designation location Name

              3 3 F

              3 3 G

              Please add this to your list and try.

              Eager to know the result.

              Thanks a lot.

               

  • 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 P

      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 P

           

          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 P

                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]
                  ;