Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

1 Solution

Accepted Solutions
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.



View solution in original post

19 Replies
Not applicable

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



prabhu0505
Specialist
Specialist
Author













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?





prabhu0505
Specialist
Specialist
Author

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?

Not applicable

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

prabhu0505
Specialist
Specialist
Author

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.

Not applicable

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

Not applicable

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

prabhu0505
Specialist
Specialist
Author

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.

Not applicable


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