Displaying messages through expressions in a list box

[Anonymous]

Hi All,

I am relatively new to Qlikview and I was facing this challenge. I have a list box displaying the products. I need to display a message saying that this product is not present in a few geographies. The geography value where the product is not in, should come from the database. I have a table in the database stating the list of products and the geographies where the product is present. But i need to display the geographies where the product is not present. I added the product as field in the list box and the wanted to play around the expression tab of the list box to show the message. Please let me know if this a achievable? How can I achieve this? Thank you for your help.

[swuehl]

You can create a text box with something like

=Concat({1<GeographyField = e() >}  DISTINCT GeographyField, ', ')

If you select a Product, the text box should display the geographies where this product is not present.

Unfortunately, this will only work correctly if you select a single poduct and it will not work as expression in the list box or table.

(Well, it may work in the list box expression with a selected product by adding a TOTAL qualifier

=Concat({1<GeographyField = e() >}  DISTINCT TOTAL GeographyField, ', ')

)

You may get it to work using a condition to check your dimension / list box field and then use a specific set expression per Product:

=Pick(Match(Product, 'A','B','C'),

Concat({1<GeographyField = e({<Product = {'A'}>}) >}  DISTINCT TOTAL GeographyField, ', '),

Concat({1<GeographyField = e({<Product = {'B'}>}) >}  DISTINCT TOTAL GeographyField, ', '),

Concat({1<GeographyField = e({<Product = {'C'}>}) >}  DISTINCT TOTAL GeographyField, ', ')

)

It would probably best to create the missing combinations of Product and GeographyField in the script, with an additional flag to filter originally existing or created data.

Something like HIC showed started from p.12 in this doc

Generating Missing Data In QlikView

with an additional flag.

Hope this helps

[Anonymous]

Is there a way we can show the expressions of a listbox only for the grayed out values conditionally?

[swuehl]

Maybe like this:

=Pick(Match(Only({1<Product = e() >}Product), 'A','B','C'),

Concat({1<GeographyField = e({<Product = {'A'}>}) >}  DISTINCT TOTAL GeographyField, ', '),

Concat({1<GeographyField = e({<Product = {'B'}>}) >}  DISTINCT TOTAL GeographyField, ', '),

Concat({1<GeographyField = e({<Product = {'C'}>}) >}  DISTINCT TOTAL GeographyField, ', ')

)

[Anonymous]

Thank you swuehl . Here the number of values are fixed and cannot be dynamic. Is there a way we can enable an expression only for the unselected values of a listbox? Please let me know.

[swuehl · Accepted solution]

You can create a dynamic expression by creating variables and dollar sign expand the variables.

See attached a sample (check the variable overview for the variables used).

Note that using this on a large number of  Products might show performance issues.

[Anonymous]

Thank you so much for your time and reply Swuehl. I added your approach to my file. The list box expression was blank and the listbox only showed th elist of my products.

So i tried to debug the same by adding the variables declared vProductList and vExpressionList in 2 different text boxes. An error popped up stating Error: Garbage after expression: ","

Then I tried to enclose the variables in single quotes, but it dint help. I got the message - Error: Garbage after expression: "%"

Am I going wrong somewhere? The same approach works perfectly in the file you sent? Is this happening to me because of more data? Please let me know.

[Anonymous]

I tried the solution you provided in the below way and it worked.

=if(len(Pick(Match( Only({1<Product = e(Product) >}Product),only({1}Product)), only({1}Product) ))>0,if(Len(Concat({1-$}Area,','))>0,'Only available in ' & Concat({1-$}Area,',')))

Thank you for your help swuehl‌

[swuehl]

What is the field name you used in your list box?

Have you replaced properly all field names with your data model field names?

Could you upload your file? (You can scramble data, or reduce your model to only the fields needed).