Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
DistinctDesignation
asDesignation1
from
[DW Model From Sar1.xlsx]
(
ooxml, embedded labels, table is Associate);
LocationTab:
Load
DistinctLocation
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?
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?
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
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.
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
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.
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