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

[#1]

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!

[Anonymous · Accepted solution]

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.

[#3]

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