Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
prabhu0505
Specialist
Specialist

OR Operator between List Boxes

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

19 Replies
prabhu0505
Specialist
Specialist
Author

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.

Not applicable

(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

prabhu0505
Specialist
Specialist
Author

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.



Not applicable

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


johnw
Champion III
Champion III

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.

prabhu0505
Specialist
Specialist
Author

Hi John,

Can you make the example personal edition compatible?

Regards,

Prabhu

johnw
Champion III
Champion III


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

Anonymous
Not applicable

I posted an example awhile ago:
http://community.qlik.com/media/p/81594.aspx

prabhu0505
Specialist
Specialist
Author

Hi Michael,

I was engaged with some other work that is the reason for the delayed reply.

I tried to access your example and John's but unfortunately it is giving the errors.

The error statement is " The file contain inconsistencies and cannot be opened by Personal Edition".

Regards

Prabhu

Anonymous
Not applicable

Strange...
Ask QlikCommunity admin to fix this. They usually make the posted files compatible with Personal Edition.