10 Replies Latest reply: May 26, 2016 2:43 AM by Tom Fack RSS

    Filter pane - show blanks

    Tom Fack

      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

          May be while loading try something like this:

           

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

            • Re: Filter pane - show blanks
              Tom Fack

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

              Data load editor.jpg

                • Re: Filter pane - show blanks
                  Sunny Talwar

                  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)

                    • Re: Filter pane - show blanks
                      Tom Fack

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

                      Auto-generated section.jpg

                        • Re: Filter pane - show blanks
                          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?

                          • Re: Filter pane - show blanks
                            Chandrasheker Gompa

                            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
                              Tom Fack

                              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
                                  Chandrasheker Gompa

                                  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
                                      Chandrasheker Gompa

                                      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
                            Tom Fack

                            Dear Chandrasheker,


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