Beast Mode With Two Dependent Criteria
New to Domo but I love it so far. I am trying to create a data table that will let me find people that have fulfilled two criteria.
Dynamic Classification Variable || Distinct count of guests that had pizza || Distinct count of guests that have had a hot dog.
Virginia || 1 || 1
North Carolina || 1 || 2
Texas || 4 || 3
I've attempted to accomplish this using two case statements.
count( distinct Case when `lunch` = 'Pizza' then `guest` end )
count( distinct Case when `lunch` = 'Hot Dog' then `guest` end )
Both of these work fine but they do not account for a rule I'll need to put in place that will limit the second set to only those that have met the first criteria. I only want to count guests that ate Pizza and a hot dog.
Here is an example of my data:
|Virginia||John Smith||Hot Dog||1|
|Virginia||John Smith||Chicken Wing||4|
|North Carolina||Iron Man||Hot Dog||1|
|North Carolina||The Doc||Pizza||1|
|North Carolina||The Doc||Chicken Wing||2|
|North Carolina||The Doc||sushi||3|
|North Carolina||The Doc||Hot Dog||4|
|Texas||Tim Tatty||Hot Dog||1|
|Texas||Red hat||Hot Dog||2|
|Texas||Mark Demark||Hot Dog||1|
Historicly I would accomplish this by creating two tables and using a left join onto a table of just the raw values.
create table one as
where lunch = 'pizza'
create table oneb as
count(distinct guest) as pizza count
group by 1
create table two as
select distinct *
where guest in ( select distinct guest from one)
and lunch = 'Hot Dog'
create table twob as
select distinct state,
group by one
left join oneb and twob to get the desired output.
Unfortunatly I don't have access to ETL's so I need to do it in a beastmode. Any help would be appreciated.