19 Replies Latest reply: May 26, 2017 12:23 PM by Wallo Atkinson RSS

    How to filter straight table

    dustin carroll

      Hi,

       

      I am sure this should be easy to do, but I can't seem to figure out the syntax needed.

       

      My raw data table has a date opened, and date closed columns. There will always be a date opened, but if the word order is not completed, the date closed will be blank. I have been able to use the nullcount function to count how many open work orders there are.

       

      But I created a table and I simple want to only display records in the table that have a null "date closed" entry.

       

      1) Can this be done without having the Date Closed dimension in my table (Since I only want to show items that have no date closed, it does not make sense to include it as a column which will never have any data)

       

      2) If not, what is the expression I should be using? I assume this needs to go in the formula box of the (Date Closed) Dimension within my table?

       

      2017-05-25 11_37_42-Qlik Sense Desktop.png

       

      I have spent about 60 minutes searching posts and working with the 600+ page PDF on scripting but nothing has worked, so any help is greatly appreciated. I am pretty tech savvy but also unfamiliar with most of the Qlik jargon which makes learning from others much harder.

       

      Thanks

        • Re: How to filter straight table
          Oleg Troyansky

          Hi Dustin,

           

          this is a tricky one... The problem is that NULL values cannot be selected directly, not in a Filter Pane, and not using Set Analysis. All you can do is select another field (Work Order, for example) that is associated with a NULL value in the field {Date Closed]. For example, like this:

           

          Count({<WorkOrder={"=isnull([Date Closed])"}>}  WorkOrder)

           

          something like this.

           

          cheers,

          Oleg Troyansky

          Check out my book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

            • Re: How to filter straight table
              dustin carroll

              Thanks for your reply.

               

              Can this be done without the count function? This is not a pivot table. I am simply trying to create a standard table that looks identical to the excel file I upload for the source data, with the one exception being I only want to see records that have a null date closed.

               

              The below screenshot is the excel file that gets uploaded, the QlikSense table I want to create will look identical however it would have all records with a date closed omitted.

              2017-05-25 12_14_55-Bruce Bochy Was Steamed About A Game-Ending Strike Call.png

                • Re: How to filter straight table
                  Oleg Troyansky

                  The count gives you the ability to filter your Work Orders based on the NULL criteria and to disable showing zeros. Without the count() function, you'd have to use IF() statements in your Dimensions that are a lot uglier and don't perform very well.

                   

                  Instead of the count() you could use Set Analysis with the Date Opened field - use it in a Measure like this:

                   

                  min( {<the same NULL condition>} [Date Opened])

                   

                  Since you only have one Date Opened per Order, the min will return the same value, and the Set Analysis will perform the filtering.

              • Re: How to filter straight table
                Wallo Atkinson

                You'd be best off creating a field in your load script.  Something like.

                 

                if(isnull([Date Closed]),'Closed','Open') as Status

                 

                Then your set analysis and filtering and everything else becomes much simpler.

                  • Re: How to filter straight table
                    dustin carroll

                    Hi Wallo,

                     

                    I am a novice user so I am clueless how to do this as you specified. But for simplicity sake, lets say I add an additional column in excel, so my new file has an additional column called "Work order status" and every record is either "Open" or "closed".

                     

                    What is the syntax to do a set analysis for a specific value (Assuming the value is not Null)? It seems like it should be simple but I continue to consume time trying to make this work on my own.

                      • Re: How to filter straight table
                        Wallo Atkinson

                        You would need to use it in all your expressions (not dimensions) in the table.

                        For instance, let's say in your table you wanted to show how many days the work order your expression is open.

                        Your script could be if(isnull([Date Closed]),'Open','Closed') as Status

                        Or you could just add it to your spreadsheet like you mentioned.

                         

                        So then let's say you want to see the number of days each order has been open.  Then you would have an expression in your table like.

                        =sum({<Status={'Open'}>}Today()-[Date Opened])

                         

                        This would filter your table down to only open work orders.  The open work orders being the ones without a Date Closed.

                         

                        And it gives you more options.  So you could also have a filter for Status that the user can click on.

                          • Re: How to filter straight table
                            dustin carroll

                            Thanks again for your help, I think I am not being clear in what I am asking.

                             

                            I do not need to have a button at the top to filter for "Open". I want to hard code this into a chart table so that the data displayed only represents records that are open.

                             

                            How can I do this? I don't under the syntax at all, what do I need to do differently?

                             

                            2017-05-25 12_14_55-Bruce Bochy Was Steamed About A Game-Ending Strike Call.png

                      • Re: How to filter straight table
                        Jahanzeb Hashmi

                        i believe there should be  many ways to do that. what about a residence load of the table with not exist

                          • Re: How to filter straight table
                            dustin carroll

                            I bet there are a bunch of ways to do this, I just need one to work though! Thanks for your reply, but I don't think that is what I am trying to do.

                             

                            How can I filter a chart table by for a specific value within a field?

                             

                            [Work Order Status] = 'Open" is showing up as an OK formula, but it keeps telling me the calculation condition is not fulfilled.

                              • Re: How to filter straight table
                                Wallo Atkinson

                                I'm not referring to a button or calculation condition.  I'm using the free version of Qlik Sense, but see if you can open this and see how I've done it.

                                  • Re: How to filter straight table
                                    dustin carroll

                                    Wallo,

                                     

                                    Thanks for taking the time to try to help me. I was able to download your file and open it myself. The table you created is exactly what I need ( I don't need the "days open" calculation, but I don't mind having it there).

                                     

                                    I simply do not understand how to create this table. Where is the formula that excludes the closed records?

                                     

                                    I have already added a new column in my excel file for the raw data for Status. So I don't need a load script. I just want to filter my chart table for Status=Open.

                                     

                                    What is the syntax?

                                     

                                    I have tried all combos I could think of, but I do not understand how the < [ { and ( are supposed to be used and it seems I just can't get the syntax correct.

                                     

                                    I am trying to attach my project as an example file but not sure how. Does this link work for someelse? https://community.qlik.com/docs/DOC-18573

                                      • Re: How to filter straight table
                                        Wallo Atkinson

                                        That link didn't work for me.

                                         

                                        The text within the {<  >} is called set analysis.  It's a way to modify your base data. 

                                         

                                        When you create a table and you click 'Add Column' you have the option of creating a Dimension or Measure. 

                                        add column.png

                                        Generally speaking a dimension would be just a field that you just want to show.  A Measure is a column that performs some kind of calculation.  By adding the Measure with the set analysis it filters the data down to just those dimensions that meet the set analysis expression.

                                        To see the Measure I added, go to Edit, then Data, then click on the column 'Days Open'.

                                        measure.png

                                          • Re: How to filter straight table
                                            dustin carroll

                                            Thanks Wallo.

                                            I definitely want everything to show up as dimensions, I want this page to look identical to the excel file I upload. The only difference is that I only want to see what is open.

                                             

                                            I want to learn how to do this without having to create a new column called "Days Open". Why can't I use the "status" column on it's own to filter so I only see records that have a status of 'Open'? Do I have to use Status as a measure instead of a dimension to accomplish this? Everytime I use status as a meausre, it is returning a -1 or 0 value which is not what I want.