topic Re: Count IF and Distinct Load in QlikView

Count IF and Distinct Load

eigorman — Sun, 08 Feb 2015 04:52:30 GMT

I am pulling together a load script for metrics reporting. I can do the consolidation of data in an expression

Count(DISTINCT{<[RTC Score]={'RED'}>}[Recovery Plan Name]

)

Then I take the totals I have in expressions

=(PlanTot-RTCSR)/PlanTot

The data I am using to bring in the score for a measurement . I bring in the RTC Score and the Recovery Plan Name fields from a resident load.

RTC:

Load [Recovery Plan Name],
[RTC Score] as RTCS,
Count(DISTINCT(IF([RTC Score]='RED',[Recovery Plan Name])))
Resident QLIK;

///// IF([RTC Score]='RED', count(Distinct[Recovery Plan Name])) as RTCS2

I am new to Qlik and still learning.

Re: Count IF and Distinct Load

Gysbert_Wassenaar — Mon, 09 Feb 2015 09:53:29 GMT

I didn't see any question in your post. But if you want to use an aggregation function like count in a load statement you will have to add the other fields you load to a group by statement:

Load [Recovery Plan Name],
[RTC Score] as RTCS,
Count(DISTINCT(IF([RTC Score]='RED',[Recovery Plan Name])))
Resident QLIK

GROUP BY [Recovery Plan Name], [RTC Score] ;

Re: Count IF and Distinct Load

Anonymous — Mon, 09 Feb 2015 10:12:27 GMT

Hi,

Try:

In Script:

RTC:
Load [Recovery Plan Name],
[RTC Score],
Count(DISTINCT(IF([RTC Score]='RED',1,0))) as RTCS2
Resident QLIK
Group By [Recovery Plan Name],[RTC Score];

Regards

Neetha

Re: Count IF and Distinct Load

eigorman — Mon, 09 Feb 2015 13:32:33 GMT

So the Recovery Plan Name has multiple entries. And RTC Score can be either Green or Red. I want to get distinct on the count of plans, where one of the RTC Score equals Red.