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.
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有