Filtering Listbox Fields

[alwayslearning]

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

[sunny_talwar · Accepted solution]

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]

Hi Kwok Li,

Is it something similar to the current selection Object ?

Thanks

[alwayslearning]

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]

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]

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

[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

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)

[alwayslearning]

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