topic OR Operator between List Boxes in QlikView

OR Operator between List Boxes

prabhu0505 — Thu, 18 Mar 2010 10:11:53 GMT

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

Thu, 18 Mar 2010 18:30:45 GMT

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

prabhu0505 — Fri, 19 Mar 2010 05:14:25 GMT

Hi Piet,

Thanks..

Load Script :

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

Directory;

LOAD

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

Designation1

from

[DW Model From Sar1.xlsx]

(

ooxml, embedded labels, table is Associate)

;

LocationTab:

Load

Distinct

Location

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

prabhu0505 — Fri, 19 Mar 2010 05:20:45 GMT

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

Fri, 19 Mar 2010 07:06:44 GMT

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

prabhu0505 — Fri, 19 Mar 2010 08:36:51 GMT

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

Fri, 19 Mar 2010 09:18:55 GMT

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

Fri, 19 Mar 2010 10:05:27 GMT

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

prabhu0505 — Sat, 20 Mar 2010 16:27:59 GMT

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

Sat, 20 Mar 2010 18:37:48 GMT


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

prabhu0505 — Mon, 22 Mar 2010 12:10:05 GMT

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

Mon, 22 Mar 2010 14:37:50 GMT

(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

prabhu0505 — Tue, 23 Mar 2010 13:49:57 GMT

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

Wed, 24 Mar 2010 18:52:45 GMT

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

johnw — Wed, 24 Mar 2010 19:30:26 GMT

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

prabhu0505 — Thu, 25 Mar 2010 05:40:57 GMT

Hi John,

Can you make the example personal edition compatible?

Regards,

Prabhu

OR Operator between List Boxes

johnw — Thu, 25 Mar 2010 17:51:50 GMT


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

OR Operator between List Boxes

Anonymous — Thu, 25 Mar 2010 17:57:35 GMT

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

OR Operator between List Boxes

prabhu0505 — Wed, 07 Apr 2010 18:57:55 GMT

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

OR Operator between List Boxes

Anonymous — Wed, 07 Apr 2010 20:16:39 GMT

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