Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
louise_atherton
Contributor III
Contributor III

Code to display only 0 values from a count in a table.

 

Hello

 

 

 

I am trying to create a table counting the number of events for each location I have logged on the system and only want to display those locations that have a value of 0 events associated with them.  Does anyone know how I would achieve this?

 

Any help would be greatly appreciated.

 

Many thanks

 

Louise

7 Replies
Anil_Babu_Samineni

Perhaps this?

Count(TOTAL <Location> {<Events = {"0"}>} Events)
Or
Count(TOTAL <Location> {<Events -= {"=Count(Events)>0"}>} Events)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
louise_atherton
Contributor III
Contributor III
Author

Thanks for responding but unfortunately they are not working.  To offer a bit of context.  I've built a table with a count on "Incident ID" with location displayed as a separate column.  I only want to display the rows  (e.g. each location) that have a count value of 0 "Incident ID".  There's an option on the system to omit nil values but not only to only include nil values. 

Tim_Wensink
Partner - Contributor III
Partner - Contributor III

Hi,

You could try this:

In your script, you could create a mapping table (say 'mapEvents2Location') containing columns location and number of events. If you then apply this to you location table by using applymap('mapEvents2Location', Location, 0) as [number of events], you can show the locations with exactly 0 events in your frontend by using count({$<[Number of events = {0}]>}[Number of events]).

Greetings,

Tim Wensink

Anil_Babu_Samineni

Better If you can post some sample data set.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rogerpegler
Creator II
Creator II

Assuming your table has Location as Dimension with an expression of Count(Incident_ID), replacing your expression with the following should give you just locations that do not have any Incident ID's.

Count({<Location={"*"}-{"=Count(Incident_ID)>0"}>}Incident_ID)

louise_atherton
Contributor III
Contributor III
Author

Thanks for responding. Though the system accepts the code it still brings up all values including those that have a count greater than 0.
rogerpegler
Creator II
Creator II

Using the sample data below I get the results per attached.  If your expression is returning everything then the -{...} part of the set analysis isn't working as expected, which might be syntax.

load * inline [
Location, Incident_ID
A, 1
B, 2
C, 3
A, 4
B, 5
A, 6
];

load * inline [
Location
A
B
C
D
E
];