topic Re: Hide values in List Box when no output in QlikView

Hide values in List Box when no output

pvanderkolk — Wed, 05 Apr 2017 14:38:55 GMT

Hi all,

I have a List Box with a field that is connected to my Straight Table. However, some of the values in the List Box don't have any outcome in the Straight Table. That's why I want to hide these values without selected output. But since the data in the Straight Table changes a lot, it can happen that one of the hidden values gets an output in the Straight Table. Obviously I want this value to be shown in the List Box again. Unchecking 'Hide Exclude Values' in the Properties menu of the List Box won't do as, next to the fact that the database changes, we can also make a selection in other List Boxes. Do you know a formula to use in the script/List Box that shows only the values in the Box that have a result in the Straight Table?

Thanks a lot!

Pim

Re: Hide values in List Box when no output

Anil_Babu_Samineni — Wed, 05 Apr 2017 16:30:46 GMT

Would you share screenshots please

Re: Hide values in List Box when no output

CarlosAMonroy — Wed, 05 Apr 2017 18:49:45 GMT

Hi Pim,

You can use the 'Where exists' clause in the script.

The definition is the following:

Table1:

Load KeySourceTable

From Table1.qvd (qvd);

Table2:

Load KeyReferenceKey, Fields

From Table2.qvd (qvd)

where exists (KeySourceTable, KeyReferenceField);

Hope that helps,

Carlos M

Re: Hide values in List Box when no output

MarcoWedel — Wed, 05 Apr 2017 22:47:26 GMT

Aggr(YourField, YourField)

Re: Hide values in List Box when no output

pvanderkolk — Thu, 06 Apr 2017 08:06:36 GMT

Hi Carlos,

Some of the values are hidden now, but not all. This is the situation I have:

My ListBox is connected to a table, which is connected to the table with my dimension from the straight table in it (as shown below)

... .... ....

A ------------------------------ A ....

... B ---------------------------------- B

... ... C

Value A is stated in a Resident tab on a script tab after the database table of column B and C. Originally his was my line in the script: Resident ..... Where Exists (C,A). But my list box was empty when using these two values, I've changed it to Resident ..... Where Exists (B,A).

FYI I'm using the resident tab because I got a syntax error when putting the 'Where Exists' clause after the 'From' statement.

Re: Hide values in List Box when no output

CarlosAMonroy — Thu, 06 Apr 2017 13:19:08 GMT

Where exist works after From or Resident statement. Would be easy if you could share your qv app.

Re: Hide values in List Box when no output

pvanderkolk — Thu, 06 Apr 2017 13:56:53 GMT

I'm afraid I'm not allowed to do this. I preferred it would have worked in the script, but I'll just change the expression of the list box. Thanks for your help.

Re: Hide values in List Box when no output

pvanderkolk — Thu, 06 Apr 2017 14:57:25 GMT

Thanks Marco. Sometimes the answer is easier than you think The sorting got messed up, but by adding an expression in the sort tab of the list box this got rectified too.