Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tomtastic
New Contributor II

Filter pane - show blanks

Hi,

Some of my records in Access are blank for the field region. When I however create a filter pane for this field in Qlik Sense there is no option to select the blanks.

I checked the properties window for the filter pane and reloaded my data to see if there was an option to exclude the blanks but no success.

Can anyone point me in the right direction?

I've attached a print screen for a visual overview of the issue.

Tags (2)
1 Solution

Accepted Solutions
gompa786
Contributor III

Re: Filter pane - show blanks

Load

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

  If(Len(Region) = 0, 'NA',Region) as Region,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`;

SQL SELECT

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

`Region`,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`

FROM `qry_REP01_02_AR`;

10 Replies
MVP
MVP

Re: Filter pane - show blanks

May be while loading try something like this:

If(Len(Trim(Region)) = 0, Dual(' ', 0), Region)

tomtastic
New Contributor II

Re: Filter pane - show blanks

I'm guessing that needs to be added to data load editor. If so, I'm getting an error.

Data load editor.jpg

MVP
MVP

Re: Filter pane - show blanks

Not here, this statement would need to go within the place where you load your data.

Capture.PNG

Go inside 'Auto-generated section' and you might have unlock the tab to make changes, but look for a table where you see region. Once you find the table, replace Region with -> If(Len(Trim(Region)) = 0, Dual(' ', 0), Region)

tomtastic
New Contributor II

Re: Filter pane - show blanks

I'm getting the following error when making a change in the auto-generated section:

Auto-generated section.jpg

MVP
MVP

Re: Filter pane - show blanks

You have an SQL Select. You cannot make edits there. Well you can but you will need to use SQL syntax and the one I provided was QlikView Syntax. You can make the changes in the preceding load (Preceding Load). If you not sure how to do that, would you be able to copy paste your script instead of pasting it as image?

gompa786
Contributor III

Re: Filter pane - show blanks

Hi Tom,

Please find a screen shot below how I used the condition on the Access database. My field Accggroup7 has no values

so I have used the below condition and after running the script the field is populated with NA..

Please mark correct answer or helpful for the sake of other members..

Thanks

Chandra

tomtastic
New Contributor II

Re: Filter pane - show blanks

Hi Sunny,

Hereby the script as requested

LIB CONNECT TO [C--Users-FackT-Desktop-DOWNLOAD-A - Bijlages-Rapportage-Databases-REP01 & REP02-REP01 - PO data.accdb];
[qry_REP01_02_AR]:

SQL SELECT
`Agmt`,
`Auto/Man`,
`Buyer`,
`Buying Channel`,
`Category 2`,
`Category description`,
`Cluster`,
`Company Name`,
`Euro price`,
`Euro value`,
`Item`,
`Item category`,
`Material Group descr`,
`Material Type`,
`MaterialNr`,
`Matl Group`,
`Name`,
`OPU`,
`OUN`,
`PC L1`,
`PC L2`,
`Per`,
`PGr`,
`PGr Scope`,
`Plnt`,
`Plnt description`,
`PO Quantity`,
`PurchDoc`,
`Region`,
`Short Text`,
`Type`,
`Type of vendor`,
`Vendor`,
`Year/Month`
FROM `qry_REP01_02_AR`;

gompa786
Contributor III

Re: Filter pane - show blanks

I have used the Accgroup7 in the filter from the above screenshot logic and it works fine for me and I can see NA also .  You just have to copy the field list from the select statement and paste on top of the select statement as above screenshot and for the field Region use this condition:

If(Len(Region) = 0, 'NA',Region) as Region

This will work

gompa786
Contributor III

Re: Filter pane - show blanks

Load

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

  If(Len(Region) = 0, 'NA',Region) as Region,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`;

SQL SELECT

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

`Region`,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`

FROM `qry_REP01_02_AR`;