How do you separate data in a column

ScottLeeds
ScottLeeds Member
in Datasets

I have a column that lists all our engagement types (call, email, meeting, text, note, and task). How do I get each engagement type to be their own column instead of all being in one? I’m trying to create a gamification points system. We want to give the salespeople 5 points for every meeting, 2 points for every call, and 1 point for every email.

I was able to do this for another data set, but that was easy because the task types were already in their own columns.

Screenshot 2023-08-31 093208.jpg
Screenshot 2023-08-31 093241.jpg
Screenshot 2023-08-31 093428.jpg

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the pivot tile to pivot your engagement type values into individual columns. This KB article will be helpful for you.
    https://domo-support.domo.com/s/article/360044951294?language=en_US

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MichelleH
    MichelleH Coach
    Answer ✓

    @ScottLeeds Is there actually a column called id in your dataset? If not, you can change those portions of the beast mode to a different field, as long as it does not contain nulls.

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    As Michelle says, I used Id since it's a column present on most of my tables that I know will never be null, so you can either replace that with a column name that is always provided or if you don't have one, you can update to use the literal number 1 instead of a column name, it'll yield the same result (supposing your dataset is fairly clean). You can also create a separate beastmode for each line without the multiplier to show the number of activities of each kind. I see your formula is using only = 'EMAIL' in the comparison while your sample data contains 'INCOMING_EMAIL' as one of the possible values, so make sure you account for those.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the pivot tile to pivot your engagement type values into individual columns. This KB article will be helpful for you.
    https://domo-support.domo.com/s/article/360044951294?language=en_US

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • marcel_luthi
    marcel_luthi Coach
    edited August 2023

    Agree with @MarkSnodgrass, pivoting the data is the way to go, and doing it via ETL when you foresee this being needed for other calculations is preferable. If this is only used for this you can go with Beast Modes, so your Score formula would become something like:

    0.5*COUNT(CASE WHEN `engagement_type` = 'OUTGOING_EMAIL' THEN `id` END)
+1.5*COUNT(CASE WHEN `engagement_type` = 'CALL' THEN `id` END)
+COUNT(CASE WHEN `engagement_type` = 'INCOMING_EMAIL' THEN `id` END)

    So basically you'll have one entry for each type of engagement that should be accounted for as part of the score.

  • ScottLeeds
    ScottLeeds Member

    @marcel_luthi thanks! you make it look so easy, I got a calculation error

    Screenshot 2023-08-31 110932.jpg
  • MichelleH
    MichelleH Coach
    Answer ✓

    @ScottLeeds Is there actually a column called id in your dataset? If not, you can change those portions of the beast mode to a different field, as long as it does not contain nulls.

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    As Michelle says, I used Id since it's a column present on most of my tables that I know will never be null, so you can either replace that with a column name that is always provided or if you don't have one, you can update to use the literal number 1 instead of a column name, it'll yield the same result (supposing your dataset is fairly clean). You can also create a separate beastmode for each line without the multiplier to show the number of activities of each kind. I see your formula is using only = 'EMAIL' in the comparison while your sample data contains 'INCOMING_EMAIL' as one of the possible values, so make sure you account for those.

Categories