Multiple input variables to select Dimension

[johnb023]

QlikView 

I have a straight table chart that displays catalogue item descriptions.

I want the user to be able to filter the description using some Input Boxes.  The user can enter 2 sets of inputs.

vSearch1 and vSearch2                 vSearch3 and vSearch4

I want to display the result of both.

WildMatch of vSearch1 and vSearch2                vSearch1 = 'filter', vSearch2 = 'element', the result would be a description that contains both 'filter' and 'element' (not 'filter' or 'element').

and the result of WildMatch of  vSearch3 and vSearch4 - same idea for this set.

The overall result would display the results of the 2 searches together in the one list.

Any or all of the variables can be blank.

= if((vSearch1 & vSearch2 & vSearch3 & vSearch4 ) = '', Descr,
if((WildMatch(Descr, '*'&vSearch1&'*') and WildMatch(Descr, '*'&vSearch2&'*')) or
           (WildMatch(Descr, '*'&Search3&'*') and WildMatch(Descr, '*'&Search4&'*'))
, Descr, null())
)

This code yields the correct result only when there is a value in each of the 2 sets.  That is, vSearch1 and vSearch3 are not blank.

[marcus_sommer]

What happens now?

if((WildMatch(Descr, '*vSearch1*') and WildMatch(Descr, '*vSearch2*')) or
           (WildMatch(Descr, '*vSearch3*') and WildMatch(Descr, '*vSearch4*'))
, Descr, null())

- Marcus

[johnb023]

It returns null/blank values for the description regardless of which combination of vSearch? is used.

I should add that I have a button that resets the values of the variables to '' (2 single quotes).

[marcus_sommer]

That results not in an empty variable. What is if you change the '' to really nothing?

- Marcus

[johnb023]

1. If I change the button reset from '' to null(), then I get blanks for the description even when nothing is entered in the input boxes.  There is a "-" displayed in the input boxes.  In this case I use:

= if(IsNull(vSearch1 & vSearch2 & vSearch3 & vSearch4, Descr,     ...)

to show all descriptions when nothing is in the input boxes.  When I use '' as the reset value, I get a full list of descriptions.

2. If I comment out the line above and only look for results from the input boxes, I get blank descriptions regardless of whether I use '' or null() as the reset value.

[johnb023]

I had a thought.  Maybe this is obvious to others, however...

The reason the list does not change until the 2nd search set has a value is:

Something1 OR * = *. 

Not until the 2nd search set has a value does the list change:  Something1 OR Something2.

[marcus_sommer]

In QlikView you could put your expression within an expression in a table-chart without defining a label for it and then by hovering with the mouse on the label QlikView showed how the expression is resolved - means here how are the variables resolved. Now you could check how it looked like when there is a search-string and if there is none respectively the '' or = null() or anything else. Just one part with a single expression should be enough to check the logic and to adjust the values respectively the syntax.

- Marcus

[johnb023 · Accepted solution]

That is a very good tip.  Thank you.  I am going to try it out.

In the meantime, I believe I resolved this using brute force.

= if((vSearch1 & vSearch2 & vSearch3 & vSearch4) = '', Descr, // if empty searches then show all

if((vSearch3 & vSearch4) = '', // if 2nd search set is empty then only use 1st search set
if((WildMatch( Descr, '*'&vSearch1&'*') and WildMatch(Descr, '*'&vSearch2&'*'),

if(vSearch1 & vSearch2 = '', // if 1st search set is empty then only use 2nd search set
if((WildMatch( Descr, '*'&vSearch3&'*') and WildMatch(Descr, '*'&vSearch4&'*')), Descr),

if((WildMatch( Descr, '*'&vSearch1&'*') and WildMatch(Descr, '*'&vSearch2&'*')) //if both sets have search criteria use both
or (WildMatch( Descr, '*'&vSearch3&'*') and WildMatch(Descr, '*'&vSearch4&'*'))
, Descr, null()
)
)
)
)