Pivot grouping issue?

Vera O
Vera O Member
edited December 2023 in General

Hi All,

I have an issue with the pivot and I'm not sure how to solve this or what function to use to get a similar arrangement!

I would like to arrange my dataset so that I see all vendors in one column grouped and all the other data columns arranged in further columns:

image.png

Normally the column selected as the "pivot ID" should be grouped (=deduplicated) but it seems that it is not grouping the values even though they are the same:

image.png


The weird thing is that for some values the group works and it is arranging it as expected:

image.png


Is there anything I can do to make the group work in the pivot?

Feel free to suggest any other solution (other than pivot)!

Kind regards,

Vera

Tagged:

Best Answer

  • Henry Simms
    Henry Simms Administrator
    Answer ✓

    Hi @Vera O thanks for clarifying.

    The Pivot custom step requires the pivot / group column to be sorted first:

    image.png

    By making this change in your example workflow I get the same number of rows (167) from both the Pivot and the Group step, so it does look to be working as expected.

    The newer Rows to Columns step aims to replace Pivot by providing additional functionality, and may be a step you want to investigate.

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Vera

    Could it be that the values are not exactly the same? Using Function Remove Noise could help remove any spaces or unprintable characters.

    Pivot is a custom step that I am not too familar with. Could you maybe start a new discussion thread (ignoring pivot) that says this is an example of my data and this is how I would like it to look in the end as there could be a couple of ways to solve?

  • Henry Simms
    Henry Simms Administrator

    Following up on what Josh says, the most common cause of the behaviour you see is a trailing space after on of the values:

    image.png

    Grouping the two vendor values would result in two groups. Using the Trim function (twice, once for leading and once for trailing space) before grouping is my go-to solution in these cases.

    Another useful function is Format Pattern to help you understand how the values differ (why they don't group). In the example above, this function will display an S to indicate a whitespace char at the end of the second value:

    image.png

    Other functions like Length can similarly be used to show difference in values that "look" the same.

  • Vera O
    Vera O Member

    Hi Henry, Josh, All,

    I've checked if there is any difference in the duplicated vendor codes, but could not find any.

    Also, I've tested and if I use the simple group function it works properly! So I assume there should be no issue with the data itself that I'm trying to group.

    Please find my test workflow attached with the dataset data:

    Testing pivot issue.dmxd

    I can also start a new thread but I was hoping that someone knows about the Pivot functionality since this should be exactly what I'm looking for... the "rows to columns" is not giving me the option to organize my data this way (or maybe only in more steps...).

    Thanks!

    Kind regards,

    Vera

  • Vera O
    Vera O Member

    Hi Henry!

    wow this worked thanks!

    Somehow the rows to columns were not grouping the columns as I wanted and how Pivot does... or maybe I do not know how to use it 😅

    But thanks I will use now the pivot with sort!

    Kind regards,

    Vera

Categories