Ifelse with multiple conditions

[Tanisha_Shetty]

Hi,

Possibly a rookie question - I’m struggling to categorise a few countries into a new field called region where there is a possible overlap in conditions.
Pls see table reference below. Is there an ifelse condition that will help me do this?

I’m trying to do this via the formula here, but with no luck as it does not even go to the E8 condition and stops at either E4/R4

ifelse(locate(“FR,IT,ES,NL”,{Country Code}) > 0,“E4”,
locate(“AU,CA,MX,BR”,{Country Code}) > 0, “R4”,
locate(“FR,IT,ES,NL,MX,CA,AU,BR”,{Country Code}) > 0, “E8”,“Others”)

Thanks,
Tanisha

[Sanjeeb2022]

Hi @Tanisha_Shetty - The result seems OK as all your country code are fall to E4 or R4. You will never have the case for E8.

What is the exact requirement, can you provide more details on the problem statement.

Regards - Sanjeeb

[Tanisha_Shetty]

Hi Sanjeeb,

I think I’m trying to reverse engineer a solution here. So we have two fields, Country and Region.
Both Region and countries both appear as filters in the dashboard and countries will be filtered basis the relevant values of Region(via a control). So if I choose E8 in the Region filter dropdown all 8 countries are chosen in the dashboard and if I choose E4 then FR,IT,ES,NL are chosen and if it region is R4 I want CA,BR,AU,MX to be chosen.
So I’m trying to create a calculated field in the dataset called Region - based on Countries . So if it is E8 then I want all the 8 countries chosen. If it is E4 I want FR,IT,ES,NL to be chosen and if it is R4 I want CA,BR,AU,MX to be chosen. Ideally I want all 3 cases to be fulfilled.

Thanks,
Tanisha

[Koushik_Muthanna]

Hi @Tanisha_Shetty ,

Checking in. Is the above topic still open and are you looking for a solution ?

Kind regards,
Koushik

[Tanisha_Shetty]

Hi @Koushik_Muthanna,

Yes, very much so. Do you have any leads?

Thanks,
Tanisha

[Koushik_Muthanna]

@Tanisha_Shetty ,

Data Generation

with countries as (
	select cast(
			json_parse('["FR","IT","ES","NL","MX","CA","AU","BR"]') as array(varchar)
		) ids
),
exploded AS (
	select 
		t.id as country,
		'B' as category,
        round(random() * 8 + 21) as count_value
	from countries
		cross join unnest (ids) t(id)
)
select * from exploded

Parameter created : region with a default value of E4

Create a calculated field : countrygrouping

ifelse(
        country = 'AU','E8,R4',
        country = 'BR','E8,R4',
        country = 'CA','E8,R4',
        country = 'MX','E8,R4',
        country = 'FR','E8,E4',
        country = 'IT','E8,E4',
        country = 'ES','E8,E4',
        country = 'NL','E8,E4',
        'Others'
)

The logic now is to check if the parameter value exists in countrygrouping or not.
Calculated field : checkgrouping

ifelse
(
    (locate(countrygrouping,${region}))>0,${region},'not found'

)

You can even link the parameter to the field checkgrouping and only show the region selected.

R4

Without any filters applied.

thanks @Gil_Raviv for the idea

Kind Regards,
Koushiki

[Sanjeeb2022]

Wow… This is an interesting solution and it will take some time for me to understand and digest Thank you @Koushik_Muthanna @Gil_Raviv

Regards - Sanjeeb

[Tanisha_Shetty]

@Koushik_Muthanna,

Checking your response only now! Very elegant - will give this a try.
Thank you and @Gil_Raviv as well!!!

Regards,
Tanisha