Group provinces by East vs West

user020987

Hi there, I'm stuck with a beast mode formula and hoping the community can help me out with this formula. 

 

CASE 'PROVINCE_CD'
WHEN 'BC' THEN 'WEST'
WHEN 'AB' THEN 'WEST'
ELSE 'EAST'
END

 

Thanks in advance! 

GrantSmith

Hi @user020987 

 

It'd be helpful if you provided a bit more context for your request. How are you stuck? What isn't working correctly? What is your expected output? What is your current output?

user020987

Initially, I created a bar chart visualization and the output was by province. After using this formula, the output became all provinces lumped under one category "East". What I would like to be able to create is a group stacked chart, where the first group is "West", and individual bars for BC and AB province. And the other group bars become "East", and the remaining provinces.

 

Thanks! 

tmullins

Your syntax for the case statement is incorrect. Based on what you have provided, the case statement should read as follows:

 

case when 'PROVINCE_CD' =  'BC' then 'WEST'

when 'PROVINCE_CD' =  'AB' then 'WEST'
else 'EAST'
end

 

or more simply,

case when 'PROVINCE_CD' in ('BC', 'AB') then 'WEST' else 'EAST' end

GrantSmith

Hi @user020987 

 

Was your code copied directly from your beast mode or did you type it here manually?

 

It appears you're using single-quotes for your field name instead of back ticks which when comparing the two strings (instead of the column value and a string) will never match so EAST is always returned.

 

Try this:

CASE `PROVINCE_CD`
WHEN 'BC' THEN 'WEST'
WHEN 'AB' THEN 'WEST'
ELSE 'EAST'
END

jaeW_at_Onyx

@GrantSmith is correct about the quotes and tildas.  

@tmullins  is correct about the actual beast mode but has the incorrect quotes and tildas.

 

CASE 
WHEN `PROVINCE_CD` IN ( 'BC' , 'AB' ) THEN 'WEST'
ELSE 'EAST'
END