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
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
-
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 :(
Just wondering if there is some efficient way of doing this. -
Ah, sorry I missed "not" statement :-) 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.
Please sign in to leave a comment.
Comments 3