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

scpradhan · 2024-10-07T22:13:18+00:00

There was an error rendering this rich post.

scpradhan

Find more posts tagged with

Accepted answers

All comments

Manasi_Panov

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.

MarkSnodgrass

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

scpradhan

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

@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

@scpradhan

The same issue is also visible for other categories:

Is there a Beast formula behind 'Store Visits'?

scpradhan

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

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.

scpradhan

Is there a way to fix this kind of issue?

Manasi_Panov

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.

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.

scpradhan

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.

' ' (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.
'' (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, ' ', ''), '', '')