using if statements in tableau on a contains function

7.8k views Asked by At

Say I have city populations for each state / city in the united states. I want to mark each city with over 2 million population as a big city. then if a state contains a "big city", i want to mark that state as a "big state". I made a calculated field:

if Pop>2000000 then "big city" else "small city" end
This works fine.

Now when I try to make a calculated field for state I tried this:

if contains([big city],"big") then "big state" else "small state" end

This almost works, but I get multiple values for each state when I only want 1 value, either big state or small state. How do I stop tableau from creating multiple values?

2

There are 2 answers

0
Manish Kr. Shukla On BEST ANSWER

You can try that with an approach involving two calculated fields.

Assuming that your big_city, small_city calculation is a calculated field named City_Size

Now, the First Calculated field will assign a 1 or 0 to each row, depeding upon the value of City_Size. Name it as is_big_state

if City_Size = 'big' then
    1
else
    0
end

Now use this calculated field into another calculation, termed as State_type

IIF(Max(City_Size) = 1, "Big State", "Small State")

Edit : You may combine both of them into one :

IIF(Max(iif(City_Size = 'big', 1, 0)) = 1, "Big State", "Small State")
3
Srini On

Have state set to "small state" by default.

When you check for city population, set the state to "big state" if you find a "big city".

if Pop > 2000000
    then "big city"
         "big state"
else
    "small city"