5 Replies Latest reply on Aug 24, 2018 2:11 PM by Okechukwu Ossai

    Count rows that have an ID matching another row, but a different value in the column

    Andrew Blake

      I have a table with two relevant columns: achievement_type and affiliate_id. Achivement_type can have values of "REFERRAL", "APP_CREATED", or "APP_FUNDED", and affiliate_id can be any number. Up to three rows can share an affiliate_id, which indicates that a person received a referral, created an application for the company's service, and got funded through that service.


      What I need to do is count the rows where achievement_type = “APP_CREATED” and where the affiliate_id of that row matches the affiliate_id of a different row that has achievement_type = “REFERRAL”. The results cannot include "APP_CREATED" rows that do not share an affiliate_id with a "REFERRAL" row.


      In the SQL database, the following query seems to get the job done:


      select count(*)from table t1

      where t1.achievement_type ='APP_CREATED'

      inner join table as t2 on t1.affiliate_id = t2.affiliate_id

      and t2.achievement_type ='REFERRAL'


      But I need to figure out how to achieve the same thing within a calculated field in Tableau. Anybody have any ideas?