Grouping the data

[SH_94]

Hi community,

I would like to ask how do we make the grouping below so that it will presnt the final result as below:

Input

Output

Thank you

[IraWatt]

Hey @SH_94,

You can use the Summarize tool by grouping by your number column and concatenating the other columns with a comer separator:

You may need to use a select tool to change your columns to text data type (vachar).

[IraWatt]

You can get a good overview of all the basic tools in the getting started learning path on the community, check out here: Learning Paths - Alteryx Community

[SH_94]

Hi @IraWatt ,

The method does not give the desired output as it will include some duplicate item. Below is the result that after following

method suggested.

THank you

[ChrisTX]

See attached workflow.  In your expected output, I think one of the values for Period is wrong.

Chris

[gautiergodard]

Hey @SH_94 

Agreed with @IraWatt that the summarize tool will help in concatenating the values like your example.

However, in your final output it looks like you only want the unique values to be represented. For this, a simple concatenation will not work as it will take every available value.

Here is a proposed solution:

[cjaneczko · Accepted solution]

See below. I used the select tool to turn everything into a Vstring. Then sorted each of the columns ascending. In the summarize tool I grouped on Number and concatenated on Year, Period, Item. Then I used formula tool with Regex Replace for each of the 3 columns you wanted concatenated. This will remove any of the duplicate values inside each of the cells. This should have everything grouped, sorted and unique for each Number. 

REGEX_Replace([Concat_Year],'\b(\w+),(?=.*\b\1,?)', '')

[ShankerV]

Hi @SH_94 

One way of doing this.

Many thanks

Shanker V

[ChrisTX]

@ShankerV after each Unique tool, I think a Sort tool is needed.  A Summarize, instead of Unique&Sort, should accomplish the same thing.

[DanielG]

@SH_94   Here is yet another way to do it.  😀  Used Multi-Row formula Tools, as those are always a bit of fun.