2 Replies Latest reply: Feb 21, 2015 8:40 AM by bruce lee RSS

    Create Listbox for Yes/No filters to count null values for a field

      Hi all,

       

      I'd like to create a listbox with Yes/No for complete/incomplete (null values) on a field which I've thrown into a table.

       

      For eg: The table in the following screenshot shows Dates in the "Completed" field can be a date or "None" for null:

       

      http://i.imgur.com/IWsOHKz.png

       

      This is because I have the following set up in my script:

       

      NULLASVALUE*;

      Set NullValue = 'None';

       

      I'm currently successfully counting what I need with the expression:

       

      =Count({<CompletedActivity = {'None'}>} CompletedActivity)

       

      However as you can see this number only displays in the title (I guess a little confusing for me here too).

       

      What I want (or perhaps any suggestions you may have for optimal functionality) is a listbox with Yes/No which filters by CompletedActivity='None' and CompletedActivity <> 'None'.

       

      I've already tried this in the script with:

       

      //Completed_Table:

      //Load * inline

      //[

      //CompletedActivity,CompletedOrNot

      //=Count({<CompletedActivity <> {'None'}>} CompletedActivity),Yes

      //=Count({<CompletedActivity = {'None'}>} CompletedActivity),No

      //];

       

      which simply craps out the raw strings in the table rather than computes a count (as per http://i.imgur.com/lZ4Geim.png).

       

      I'm new to qlik but am currently stuck so I would greatly appreciate your suggestions by way of a demonstration for my learning purposes - thank you so much!

        • Re: Create Listbox for Yes/No filters to count null values for a field
          Chris Deniziak

          You can create a table by doing a resident load on your previously loaded table in the script. It would look similar to the attached. You can do things a bit differently based on how you want to group, but the overall idea is there.

           

          Let me know if you need further explanation.

            • Re: Create Listbox for Yes/No filters to count null values for a field

              Thank you very much good sir!!

               

              Your solution will be marked as correct but just as a caveat to others: My 'completedactivity' was a date and I could see the example values that Chris put in the load were example numbers - which clearly would work when CompletedActivity = 'None' but not when it wasn't :-)

               

              For Chris: I dunno if this was a long-winded solution but I simply created another date field and ran the month() function on it (first function that popped into my head), then I populated the LOAD statement as:

               

              YourTable:

              LOAD * INLINE [

                  CompletedMonth

                  'None'

                  1

                  2

                  3

                  4

                  5

                  6

                  7

                  8

                  9

                  10

                  11

                  12

              ];

               

              Which took into account all possible date values to filter (as there are only 12 months )

               

              This is most likely not the cleanest approach to a solution but it works - if any others have any suggestions I am as ever open and grateful!

               

              Best

               

              raz