Conditional Count in Aggregate

CloverDX Platform
1

How can I do conditional counts in Aggregate Transformation

for SQL eg:

,count(case when
case.case_status not in (‘Denied’)
then case.case_status
end )as applied_ytd_tot
,count(case when
case.case_status in(‘Denied’)
then case.case_status
end )as rejected_ytd_tot

2

Hi visasquare,

what about put ExtFilter before Aggregate which will filter out unwanted records?

3

Thank you for the response. That is what I did, put a ext filter

But I want both the counts? Hence i need to write two aggregators reading data from two ports of ext filter and then merging them together using a merge

This looks like a lot of work and redundant code for a simple SQL :frowning:

Just wondering if there is some efficient way of doing this.

4

Ah, sorry I missed “not” statement :slight_smile: Then you could prepend Reformat component, which will transform record e.g. [id, case_status] to [id, case_status_a, case_status_b] based on condition in CTL2 code. One field per record will be filled by actual value, second by something harmless (0 for SUM for example). Then you use 2 aggregation functions in Aggregate.