topic Re: Filter pane - show blanks in App Development

Filter pane - show blanks

Anonymous — Mon, 23 May 2016 13:41:08 GMT

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.

Re: Filter pane - show blanks

sunny_talwar — Mon, 23 May 2016 15:22:57 GMT

May be while loading try something like this:

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

Re: Filter pane - show blanks

Anonymous — Tue, 24 May 2016 07:19:09 GMT

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

Re: Filter pane - show blanks

sunny_talwar — Tue, 24 May 2016 09:14:11 GMT

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)

Re: Filter pane - show blanks

Anonymous — Tue, 24 May 2016 11:05:45 GMT

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

Re: Filter pane - show blanks

sunny_talwar — Tue, 24 May 2016 11:25:13 GMT

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?

Re: Filter pane - show blanks

Anonymous — Tue, 24 May 2016 13:26:55 GMT

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

Re: Filter pane - show blanks

Anonymous — Wed, 25 May 2016 12:37:06 GMT

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

Re: Filter pane - show blanks

Anonymous — Wed, 25 May 2016 15:30:00 GMT

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

Re: Filter pane - show blanks

Anonymous — Wed, 25 May 2016 15:39:19 GMT

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

Re: Filter pane - show blanks

Anonymous — Thu, 26 May 2016 06:43:14 GMT

Dear Chandrasheker,

Now I got it working. What I did earlier was put in the if statement in the SQL SELECT.