Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pashok75
Contributor II
Contributor II

How to use an IF condition and count the records

Dears,

I am having an requirement to apply an IF condition on a field and once its True I need to do a count on another field.

=if(T_TOR_CODE = 'CC', count(distinct T_STATUS = 'APPROVED'))

in the statement  I want if the T_TOR_CODE = 'CC'  then I need to count on a field where T_STATUS = 'APPROVED'

Ashok

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Remove Distinct from your expression, even if you have 1000 rows of Approved the Distinct value will be 'Approved' so the count returned will always be 1

Just remove Distinct

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} T_STATUS)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

11 Replies
marcus_sommer

Try something like this:

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} distinct T_STATUS)

- Marcus

pashok75
Contributor II
Contributor II
Author

Dear Marcus,

Thank you so much for your prompt response. However while running I am getting value as 1. As you distinct T_Status its either approved or not approved.  I try to distinct T_TOR_CODE I need to get the count of 'CC' but its returning 0.

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} distinct T_STATUS)

marcus_sommer

It's not quite clear for me what do you try to achieve. Please provide some more backgrounds to your task, for example screenshots from the tableviever and from your charts and which results do you expect.

- Marcus

pashok75
Contributor II
Contributor II
Author

In the T_TOR_CODE field we have 'CC', 'TT', 'WW' and in teh T_Status we have 'APPROVED', 'NOT APPROVED'

Table looks like this

T_TOR_CODE               T_STATUS

'CC'                                        APPROVED

'CC'                                   NOT APPROVED

'CC'                                        APPROVED

'TT'                                   NOT APPROVED

'TT'                                   APPROVED

'WW'                                   APPROVED

'WW'                                   NOT APPROVED

When I run this condition

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} distinct T_STATUS)

Then I should get for 'CC' count as 2 for approved.

Hope its clear for you.

Ashok

pashok75
Contributor II
Contributor II
Author

the last line got cut

when I do a count on T_STATUS FOR 'CC'  it should return be 2 as two records are there for 'APPROVED'

marcus_sommer

In this case just remove the distinct key-word:

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} distinct T_STATUS)

- Marcus

pashok75
Contributor II
Contributor II
Author

Distinct of status will not wok as it will return value as 1. As in the T_Status the value is either Approved or Not Approved.

I think we can achieve this by an IF condition.

Ashok

vinieme12
Champion III
Champion III

Remove Distinct from your expression, even if you have 1000 rows of Approved the Distinct value will be 'Approved' so the count returned will always be 1

Just remove Distinct

count({< T_TOR_CODE = {'CC'}, T_STATUS = {'APPROVED'}>} T_STATUS)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pashok75
Contributor II
Contributor II
Author

Dear Vineeth,

How abt to run the same syntax other than 'APPROVED'

=count({< T_TOR_CODE = {'DP'}, T_STATUS <> {'APPROVED'}>} T_STATUS)

<> is not working.

Ashok