How to use an IF condition and count the records

[pashok75]

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

[marcus_sommer]

Try something like this:

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

- Marcus

[pashok75]

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]

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

Re: How to use an IF condition and count the records

[pashok75]

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]

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 · Accepted solution]

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)

[pashok75]

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