Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
kvijay173
Creator
Creator

Count if the Occurrence is not in Multiple state

Hi Friends,

I have a data with Computer Name, Installation Status, Health State. Some computers will have the status as INSTALLED and not FAILED. But some computers will have both INSTALLED and FAILED.

Capture.PNG

I would like to create a table for Health State as below. If you look at the below result the total computers is 9 (distinct). But 5 computers are a member of Installed status only. I already have a similar report in Tableau. But now we are moving on to QlikView from Tableau. I dont know how to achieve this in QlikView.

1.PNG

1 Solution

Accepted Solutions
sunny_talwar

May be like this (assuming all the above your mentioned are un-healthy)

Dimension

If(Match(Status, 'Failed', 'Not Installed', 'Downloaded'), 'Un-Healthy', 'Healthy')

Expression

=Count(DISTINCT {<[Computer Name] = e({<Status = {'Failed', 'Not Installed', 'Downloaded'}>})>+<Status = {'Failed', 'Not Installed', 'Downloaded'}>} [Computer Name])

View solution in original post

13 Replies
sunny_talwar

Create a straight table

Dimension

Health State

Expression

Count(DISTINCT [Computer Name])

sunny_talwar

May be this

=Count(DISTINCT {<[Computer Name] = e({<[Health State] = {'Un-Healthy'}>})>+<[Health State] = {'Un-Healthy'}>} [Computer Name])

Capture.PNG

vinieme12
Champion II
Champion II

Alternate Exp  , i'd prefer Sunny's though...posting only for reference

=count({<[Computer Name] = {"=count(DISTINCT [Health State])=1"},[Health State] = {'Healthy'}>+<[Health State]={'Un-Healthy'}>}

DISTINCT [Computer Name])

kvijay173
Creator
Creator
Author

Friends, the 3rd column will not be there.. I just put there for your reference to show which statuses are considered as Healthy and Un-Healthy.

I actually have only the first 2 columns

vinieme12
Champion II
Champion II

So just this then!

=count({<[Computer Name] = {"=count(DISTINCT [Status])=1"} , [Status] = {'Installed'}>}

DISTINCT [Computer Name])

sunny_talwar

May be this then

Dimension

If(Status = 'Failed', 'Un-Healthy', 'Healthy')

Expression

=Count(DISTINCT {<[Computer Name] = e({<Status = {'Failed'}>})>+<Status = {'Failed'}>} [Computer Name])

sunny_talwar

Sample attached

Capture.PNG

sasiparupudi1
Master III
Master III

Try

kvijay173
Creator
Creator
Author

Hi Sunny,

Thanks it is working. What to do if I have more statuses to be grouped inside Un-Healthy?

For Example: If I have Failed, Not Installed, Downloaded - All these has to be included in the below script. How to do this.. Thanks again


=Count(DISTINCT {<[Computer Name] = e({<Status = {'Failed'}>})>+<Status = {'Failed'}>} [Computer Name])