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

aarguijo
aarguijo Member
in Beast Mode

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?

Screenshot 2023-03-22 at 1.02.33 PM.png

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    aarguijo Member

    @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
    MichelleH Coach

    @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
    aarguijo Member

    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
    MichelleH Coach

    @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

Categories