Beast Mode With Two Dependent Criteria

user05225

Hey,

 

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.

 

Current Output: 

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: 

State	Guest	Lunch	Day
Virginia	John Smith	Hot Dog	1
Virginia	John Smith	Hamburger	2
Virginia	John Smith	Pizza	3
Virginia	John Smith	Chicken Wing	4
Virginia	John Smith	Pizza	5
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	Tim Tatty	Fruit	2
Texas	Tim Tatty	Beef	3
Texas	Red hat	Pizza	1
Texas	Red hat	Hot Dog	2
Texas	Blue Boy	Pizza	1
Texas	Mark Demark	Hot Dog	1
Texas	Mark MallBurg	Pizza	1
Texas	Mat Hammond	Pizza	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

select *

from master 

where lunch = 'pizza'

 

create table oneb as 

select distinct 

state,

count(distinct guest) as pizza count

from one 

group by 1 

 

create table two as 

select distinct *

from master 

where guest in ( select distinct guest from one)

and lunch = 'Hot Dog' 

 

create table twob as 

select distinct state,

count(distinct guest)

from two

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. 

 

NewsomSolutions

It's late and I may not have read through everything correctly but follow me here.  If you don't have access to ETL and your beast modes are only doing one peice to a two piece calculation.  Why don't you just leave two BM...one for a flag for 'pizza' and one a flag for 'hot dog'...then do a filter on your card for those that have only those two flags?  That may be a very over simplistic way to solve this...but wanted to throw it out. - matt