Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alwayslearning
Creator
Creator

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
sunny_talwar

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

View solution in original post

17 Replies
sunny_talwar

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
Contributor III
Contributor III

Hi Kwok Li,

Is it something similar to the current selection Object ?

Thanks

alwayslearning
Creator
Creator
Author

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?

sunny_talwar

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
Creator
Creator
Author

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. 

sunny_talwar

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
Creator
Creator
Author

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

sunny_talwar

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
Creator
Creator
Author

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