How do I replace a figure in m pivot table with a letter

honeybee_tee
honeybee_tee Member
in Beast Mode

I've written this syntax to calculate a new column called Priority companies
COUNT(DISTINCT CASE WHEN
(Company Name IS NOT NULL)
AND ((Activity types LIKE '%attended event%' )

OR (Activity types LIKE '%email engagement%')

OR (Activity types LIKE '%webcast attended%')

OR (Activity types LIKE '%webpage visit'))

AND (distribution team LIKE '%marketing')

AND (salesforce contact record type LIKE '%Institutional/ Third Party')

THEN Company Name

END). I added it to a pivot table, and I had values like 1 & 0. If I want to make sure the values 1 are shown as Priority, how do I do that?

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a case statement to convert the 1 to 'Priority':

    CASE WHEN `field` = 1 THEN 'Priority' ELSE 'Non-Priority' END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach
    Answer ✓ 

    CASE 
    
    WHEN COUNT(DISTINCT CASE 
    
        WHEN 
    
            (Company Name IS NOT NULL)
    
            AND (Activity types LIKE '%attended event%' 
    
                OR Activity types LIKE '%email engagement%'
    
                OR Activity types LIKE '%webcast attended%'
    
                OR Activity types LIKE '%webpage visit')
    
            AND (distribution team LIKE '%marketing')
    
            AND (salesforce contact record type LIKE '%Institutional/ Third Party')
    
        THEN Company Name
    
    END) = 1 THEN 'Priority'
    
    ELSE 'Non-Priority'
    
END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a case statement to convert the 1 to 'Priority':

    CASE WHEN `field` = 1 THEN 'Priority' ELSE 'Non-Priority' END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach
    Answer ✓ 

    CASE 
    
    WHEN COUNT(DISTINCT CASE 
    
        WHEN 
    
            (Company Name IS NOT NULL)
    
            AND (Activity types LIKE '%attended event%' 
    
                OR Activity types LIKE '%email engagement%'
    
                OR Activity types LIKE '%webcast attended%'
    
                OR Activity types LIKE '%webpage visit')
    
            AND (distribution team LIKE '%marketing')
    
            AND (salesforce contact record type LIKE '%Institutional/ Third Party')
    
        THEN Company Name
    
    END) = 1 THEN 'Priority'
    
    ELSE 'Non-Priority'
    
END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories