Any way to get text value from one column based on text value of another column?

aarguijo · 2023-03-22T18:07:21+00:00

There was an error rendering this rich post.

I've got a data set that is a series of user and assistant responses. I'm trying to build a table visualization that will show me all user questions where the assistant did not have an answer.

My thought was that I could search for the default “no answer” response in the assistant response columns, but am stuck at how to then return the user question that prompted the “no answer response" as a value that I can display in a table.

I was attepting to use

CASE WHEN DP DP Response 2 = ‘DP: Sorry, I don't have an answer for that.’

THEN User Question 2

END

but kept getting syntax errors and not sure if that's just not possible w/ text or if I've missed something completely. Also realize I may not be able to use the full text due to the use of apostrophe so will likely need to try to search by contains ‘DP: Sorry’ but Beast Mode doesn't seem to recognize CONTAINS?

Screenshot of one of these occurences within the data attached. Help?

Find more posts tagged with

Cards

Table

Dashboard

Card Builder

Beast Mode

Accepted answers

MichelleH

@aarguijo The apostraphe is likely what's causing the syntax error. Instead of CONTAINS, you would want to use the LIKE operator like this:

case when DP Response 2 LIKE ‘DP: Sorry%’ then User Question 2 end

All comments

MichelleH

@aarguijo The apostraphe is likely what's causing the syntax error. Instead of CONTAINS, you would want to use the LIKE operator like this:

case when DP Response 2 LIKE ‘DP: Sorry%’ then User Question 2 end

aarguijo

@MichelleH that did help with the syntax error, but somehow I'm getting no data returned with that formula as a whole. Do you know if I'm missing something that would actually pull through the value of the “User Question 2” column?

MichelleH

@aarguijo Does it still return no values if you change it to this?

case when DP Response 2 LIKE ‘DP: Sorry%’ then 1 end

If you see 1s populating, then that means there's not data in the User Question 2 field. If not then there's something wrong with the condition, for example there are extra spaces in the string you're searching

aarguijo

Ah I figured it out. It wanted a % at the start of the string as well - so

case when DP Response 2 LIKE ‘%DP: Sorry%’ then User Question 2 end

Thanks so much for your help!

MichelleH

@aarguijo I'm glad you got it to work! Please be sure to mark my response as the answer so that others can find answer more quickly