Calculated Field- What formula to see if column A contains column B?

Question & Answer
,
1

Hello! I am trying to create a field to see if the first/last name is in an email. These are separate columns in the report.
Ideally I would be able to create a field that would populate “1” if the first name is found, “2” if the first and last is found and 0 if the first/last is not in the email. I keep getting errors and I’ve tried both count if, contains, and locate. How should I format this? Any help is appreciated!

2

Hi @sjones.3795 - Welcome to AWS QuickSight community and thanks for posting the question. This is strange, is it possible to share the calculation field definition/formula that you are trying to add. This will help me replicate the issue at my side.

3

Thanks for the response! This is what I have been trying but it doesn’t work, it just populates 0 for everyone. This is the only formula that hasn’t given me an error. I’ve also tried COUNTIF and LOCATE but keep getting errors.

contains(email,‘{firstname}’,CASE_INSENSITIVE)

The goal would be to have it flag if it contains first and last name (separate columns in my report) but I understand if I would have to separate the calculated fields. Thanks for your help!

4

Hi Sydney - Thanks for your response. I tried to recreate the formula at my side and able to run without any errors. Pls use locate and If Else to achieve this. Can you pls try and let me know if it helps?

ifelse((locate({email},{firstname})>0),‘Found’,‘Not Found’)

Here is the link to documentation: Ifelse - Amazon QuickSight

5

Hi @sjones.3795 - you are on the right track! You uncovered a couple nuances with the contains and locate functions that I wasn’t aware of until trying this out :slight_smile: One is that the contains function wants a string literal as the substring to look for and wont accept a field there (which I presume is why you wrapped it in quotes, but then it looks for that exact text). Locate is another option but it doesn’t support the CASE_INSENSITIVE mode, so we will account for that by wrapping fields with toLower().

@dmalaag 's formula is exactly what we need, I just built on that slightly to return multiple findings from the same ifelse.

Try this:

ifelse(
locate(toLower({product}), toLower({first name}))>0 and locate(toLower(product), toLower({last name}))>0, 3,
locate(toLower({product}), toLower({first name}))>0, 2,
locate(toLower({product}), toLower({last name}))>0, 1,
0)

In that example finding both returns 3, finding first only returns 2, findind last only returns 1, finding neither returns 0.

6

Hi thanks for your help! Not sure what I am doing wrong but I am getting errors trying to use both functions. Where am I messing it up?
ifelse((locate({email},{firstname[federal_all_positions]})>0),‘Found’,‘Not Found’)

and

ififelse(locate(toLower({email}), toLower({firstname[federal_all_positions]}))>0 and locate(toLower({email}), toLower({lastname[federal_all_positions]}))>0, 3, locate(toLower({email}), toLower({firstname[federal_all_positions]}))>0, 2, locate(toLower({email}), toLower({lastname[federal_all_positions]}))>0, 1, 0)

I keep getting errors so I tried tailor it a little bit but it still doesn’t work. Thanks for your help!

7

Can you pls send details of error you are getting?

8

It just says “Errors found”.

9

I will check your formula and confirm back in some time. In the meantime, pls validate that there are no extra spaces and all brackets that open, do close as well.

1 Like