I’m working on a simple categorization that’s needed in a dimension/measure but not able to do it as it’s a large table and several joins and nuances are involved in the same.
In my Explore, I have:
Now, I need to create buckets for Teams that have 1 member, 2-10 members and so on.
This can be done very simple by case-when statement and I already did it in PostgreSQL
select size_bucket, count(distinct team_id) from (select distinct team_id, case when count(distinct member_id) = 1 then '1 member' when count(distinct member_id) >= 2 and count(distinct member_id) < 11 then '2-10 members' when count(distinct member_id) >= 11 and count(distinct member_id) < 50 then '11-50 members' when count(distinct member_id) >= 50 then '50+ members' else null end size_bucket group by 1 ) a group by 1;
However, whenever I go about doing this in the LookML by “size_bucket” new dimension - I get errors and issues with Count(distinct ) of that ID as it cannot be done without errors.
I can’t declare the “size_bucket” as measure, as output is in String.
Kindly help me out in what part of the approach is wrong here and how I can get this basic SQL query working in LookML and Looker dashboards