topic Re: Filtering Listbox Fields in QlikView

Filtering Listbox Fields

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am new to Qlikview.

I am creating a list box to show '$Field', which is all the column names in my raw data. There are a lot of columns but I only want a few to appear how can I select just the few I want to be visible and selectable on the listbox.

My ultimate goal is to create a table, where based on the condition that the user selects the Field from the list box it will then appear or now. I have been able to do the Conditional show/hide part.

Regards,

Qlikview Novice

Re: Filtering Listbox Fields

sunny_talwar — Fri, 23 Sep 2016 22:44:32 GMT

If these fields are from a particular table, you can use $Table field to restrict the field names from a particular table

Aggr(Only({<$Table = {'Product', 'Customer'}>} $Field), $Field)

You can use an if statement if the number of fields are just handful.

If(Match($Field, 'ProductID', 'ProductName', 'CustomerID', 'CustomerName'), $Field)

There might be few other ways, but will depend on what exactly you are looking to do

Re: Filtering Listbox Fields

soloeeeoff — Mon, 26 Sep 2016 07:05:23 GMT

Hi Kwok Li,

Is it something similar to the current selection Object ?

Thanks

Re: Filtering Listbox Fields

alwayslearning — Mon, 26 Sep 2016 12:57:28 GMT

Thanks Sunny.

I've used the Match one as that work's perfectly for me.

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

To clarify the Aggr function does 'Product', 'Customer' represent table names? Where do I put the columns names to use?

Re: Filtering Listbox Fields

sunny_talwar — Mon, 26 Sep 2016 17:03:11 GMT

It will pick all the columns from those tables, but if you want specific columns from those tables, you can do this

Aggr(Only({<$Field = {'ProductID', 'ProductName', 'CustomerID', 'CustomerName'}>} $Field), $Field)

Re: Filtering Listbox Fields

alwayslearning — Mon, 10 Oct 2016 01:49:12 GMT

I have a follow up.

On my custom Listbox

If(Match($Field, 'ProductID', 'ProductName', 'CustomerID', 'CustomerName'), $Field)

I want to now filter by particular values. And I need it to filter by different column than in the Match expression

Using the example of the Match Expression, There are Nulls that exist in 'ProductName' and I want when I click on the Product Name filter for it to return just the Null Values.

Re: Filtering Listbox Fields

sunny_talwar — Mon, 10 Oct 2016 12:10:12 GMT

When you select ProductName, you see null in ProductName field? Seems like you would need to add a trigger to do this. Would you be able to elaborate a little on this requirement?

Re: Filtering Listbox Fields

alwayslearning — Wed, 12 Oct 2016 14:55:04 GMT

To expand on my above question: For example if we had this table

My first question was how to have a Listbox of it certain columns, the answer is write an expression like :

If(Match($Field, 'CustomerID', 'CustomerName', 'Price', 'Sale Price'), $Field)

Now when I click on the listbox and it gives me that data, I only want it to return the blanks. I.e. if I click on Price it will return just Row 3, if I then clicked on multiple items like Price and Order it would return rows 1,2,3 & 5. Of course if I included CustomerID in my selection it would return all as it has no blanks.

If it helps in my actual table, I have also created a column which identifies blanks with a value of 1 and none with 0 (for other expressions I needed). These are actual Null Values

ProductID	ProductName	CustomerID	CustomerName	Price	Sale Price	Order
1	Trainers	1	Shop1	50	5
2	Shoes	2		100	50
3	Boots	3	Shop3
4	Hiking Boot	4		10	5	3
5	Slippers	5	Shop5	10	2

Thanks

Re: Filtering Listbox Fields

sunny_talwar — Wed, 12 Oct 2016 18:07:27 GMT

Here check this out

1) No selection - Shows nothing (Not sure if this is desirable or if you would like to see everything when nothing is selected

2) When Price is selected - Row 3 is visible

3) When Price and Order are selected -

Not sure why would it return all rows if no blank is included? Is that a rule that if there are blanks in a column show them, else show everything?

Dimensions

ProductID

ProductName

CustomerID

CustomerName

Price

Sale Price

Order

Expression

=Avg({<ProductID = {$(=Chr(34) & '=' & Concat('Len(Trim(' & $Field & ')) = 0', ' or ') & Chr(34))}>} 1)

Re: Filtering Listbox Fields

alwayslearning — Wed, 12 Oct 2016 21:13:39 GMT

Hi,

Your example seems to work in some of the cases and is what I am looking for. When you click Sale Price though it is blank.

Could you briefly break down the formula you have wrote, so I can understand it? As it is the same in my real dashboard I am building, it works in some cases but not all. I see you used concat? Is that for when cells are blank? Or actually nulls? As to find blanks in my report, I have been using ISNULL mostly.

also for number 1 of the above, if nothing is selected I would by default like to show all the blanks of all those fields.

Ultimately I am trying to determine where is the null data in all specific records

Re: Filtering Listbox Fields

sunny_talwar — Wed, 12 Oct 2016 21:19:53 GMT

I think since sales price is two words, it needs square brackets around it. Can you check this:

=Avg({<ProductID = {$(=Chr(34) & '=' & Concat('Len(Trim([' & $Field & '])) = 0', ' or ') & Chr(34))}>} 1)

Test this out and see if its working or not and I will get back to explaining this sometime today evening.

Re: Filtering Listbox Fields

sunny_talwar — Wed, 12 Oct 2016 21:22:00 GMT

Also look at this attached sample to look at all the nulls in each of your field.

Re: Filtering Listbox Fields

alwayslearning — Wed, 12 Oct 2016 21:40:30 GMT

Thanks very much Sunny. This has seemed to given me what I need.

Re: Filtering Listbox Fields

alwayslearning — Wed, 12 Oct 2016 21:41:59 GMT

I decided to in my load scripts create nullversion of my columns i.e. if null =1 and then I summed them up in expressions to get total null values.

Re: Filtering Listbox Fields

ziabobaz — Wed, 08 Feb 2017 14:43:53 GMT

Hi!

I tried your expression and it is not working as I want it to work.

I want to hide one of the Fields in the filter pane so the user won't see it.

I put the following formula:

Aggr(Only({<[Field]-={'Text'}>} [Field]), [Field])

It hides the Text field while no selection is made - it's good.

Once i make a selection of the other fields, it hides the rest of the fields - that's bad.

Any idea?

Re: Filtering Listbox Fields

sunny_talwar — Wed, 08 Feb 2017 14:46:10 GMT

Try this (moving the placement of minus sign after =:

Aggr(Only({<[Field] = -{'Text'}>} [Field]), [Field])

Re: Filtering Listbox Fields

ziabobaz — Wed, 08 Feb 2017 15:23:32 GMT

Great!

Although I don't understand why.

"-=" is the common expression for NOT EQUAL

Re: Filtering Listbox Fields

sunny_talwar — Wed, 08 Feb 2017 15:45:28 GMT

Slight difference

Field1 is composed of ABC, DEF, GHI and we have selected ABC & DEF

Field1 -= {'ABC'} means exclude ABC from Field1 based on selection

Will show DEF

Field1 = - {'ABC'} (is equivalent to Field1 = {'*'} - {'ABC'}) means no matter what the selection is

Will show DEF & GHI