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,
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.
(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
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.
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
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.
Hi John,
Can you make the example personal edition compatible?
Regards,
Prabhu
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]
;
I posted an example awhile ago:
http://community.qlik.com/media/p/81594.aspx
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
Strange...
Ask QlikCommunity admin to fix this. They usually make the posted files compatible with Personal Edition.