Filter pane - show blanks

[Anonymous]

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.

[sunny_talwar]

May be while loading try something like this:

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

[Anonymous]

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

[sunny_talwar]

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

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)

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[Anonymous]

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`;

[Anonymous]

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

[Anonymous · Accepted solution]

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`;