Can I know why the data is not grouping into single column in the pivot table?

Answers

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @scpradhan,

    Consider using the SQUASH_WHITESPACE() function in the ETL process to address potential discrepancies like "Backpacks/Bags" vs "Backpacks/Bags " with space at the end.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Do you have anything in the sorting section in Analyzer? That will keep like items from being grouped together.

    **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.
  • scpradhan
    scpradhan Member

    Hi @Manasi_Panov , I tried using SQUASH_WHITESPACE() function. It didn't help.

    image.png

    @MarkSnodgrass , there aren't any fields on the sorting section. I am not sure what is causing the field not to be grouped into one.

  • Manasi_Panov
    Manasi_Panov Contributor

    @scpradhan

    The same issue is also visible for other categories:

    image.png

    Is there a Beast formula behind 'Store Visits'?

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • scpradhan
    scpradhan Member

    Yes, there are other categories. There is no beast mode formula used on the metrics. I checked on the other metrics like spend and clicks. And it's not grouping correctly by any other metrics.

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @scpradhan,

    If it is not the SQUASH_WHITESPACE() function on the 'Category' column, then could the issue be related to keyboard or language settings affecting categories names when they were created? In a test I conducted, replacing some Latin letters with Cyrillic letters resulted in unexpected grouping behavior.

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • scpradhan
    scpradhan Member

    Is there a way to fix this kind of issue?

  • Manasi_Panov
    Manasi_Panov Contributor

    Hi @scpradhan,

    First, verify if this applies to your data. A quick test is to copy and paste both categories into MS Word or another tool to check for any replaced letters, as in my case.

    image.png

    The underlined word is the issue, as the letter "a" was replaced with the Cyrillic "a". You can then create a CASE statement (I'll bold the Cyrillic "a").

    CASE WHEN 'Categories' = "Part" THEN "Part" ELSE 'Categories' END

    Or you can just create the CASE statement randomly choosing one of the categories.

    Another approach I've done is with Python, but it is way more complicated and I don't think it is worth the effort.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • scpradhan
    scpradhan Member

    I found the solution for this as there were (Non-breaking Space, ASCII 160) and (Zero Width Space, Unicode U+200B) characters in the text. So, replacing those characters helped.

    1. ' ' (Non-breaking Space, ASCII 160)
      Non-breaking space (' ') is a type of space that looks visually like a regular space but has a different Unicode value (ASCII 160).
      It is often used in HTML or other formats to prevent text from breaking across lines, which can cause issues in your data when it's not cleaned properly.
      In this case, ' ' represents the non-breaking space character.
    2. '​' (Zero Width Space, Unicode U+200B)
      Zero-width space ('​') is an invisible character that has no width. It’s often used in data formatting or when dealing with text that needs to appear connected but actually contains invisible boundaries.
      The Unicode character U+200B is represented as '​' (a zero-width space).

    Solution:

    REPLACE(REPLACE(Category, ' ', ''), '​', '')

Categories