Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
alwayslearning
Contributor

Filtering Listbox Fields

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

1 Solution

Accepted Solutions

Re: Filtering Listbox Fields

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

17 Replies

Re: Filtering Listbox Fields

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

soloeeeoff
New Contributor III

Re: Filtering Listbox Fields

Hi Kwok Li,

Is it something similar to the current selection Object ?

Thanks

alwayslearning
Contributor

Re: Filtering Listbox Fields

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

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)

alwayslearning
Contributor

Re: Filtering Listbox Fields

Hi

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

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?

alwayslearning
Contributor

Re: Filtering Listbox Fields

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

      

ProductIDProductNameCustomerIDCustomerNamePriceSale PriceOrder
1Trainers1Shop1505
2Shoes210050
3Boots3Shop3
4Hiking Boot41053
5Slippers5Shop5102

Thanks

Re: Filtering Listbox Fields

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

Capture.PNG

2) When Price is selected -  Row 3 is visible

Capture.PNG

3) When Price and Order are selected -

Capture.PNG

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)

alwayslearning
Contributor

Re: Filtering Listbox Fields

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