How to do groupby with having clause in Alteryx

Question

I'm trying to build a workflow which would do the below operation in Alteryx? Should I use In-Db or any other tool like summarize tool to do group by and having. I was able to do the group by using summarize tool, but not able to perform having.

select memberId, procedureCode
from claims
group by memberId, procedureCode
having count(distinct year(ServiceDate) * 100 + month(ServiceDate)) > 4;

Thableaus · Accepted Answer

Hi @monish_chandra

I recommend you to break up your SQL statement in parts.

First, create a field using Formula Tool to do this:

year(ServiceDate) * 100 + month(ServiceDate)

then, use Summarize Tool to Group By (Member Code, Procedure Code) and Count your Formula Field previously created.

Then, after all, filter your Count (Formula Field) > 4.

You should have your final result.

Cheers,