Grouping by rows

[alex_reevoo]

Hi All,

Have a question

I have data like the below, the purpose of this report is to find the most popular products betwee multiple retaielr

RETAILER PRODUCT NAME

Retailer 1      PRODUCT 1

Retailer 1      PRODUCT 2

Retailer 1      PRODUCT 3

Retailer 1      PRODUCT 4

Retailer 1      PRODUCT 5

Retailer 2      PRODUCT 1

Retailer 2      PRODUCT 2

Retailer 2      PRODUCT 3

Retailer 2      PRODUCT 4

Retailer 2      PRODUCT 5

I'd like to convert this (potentially with transpose I think?) so that the data looks like this:

RETAILER 1  RETAILER 2

Product 1       Product 1

Product 2       Product 2

Product 3       Product 3

Product 4       Product 4

Product 5       Product 5

How would I go about ensuring that the retailer names are now the headers?

Cheers!

Alex

[MikeA]

Hi Alex,

To get your data into your desired format, you could use the Cross Tab tool. In the properties of the cross tab tool you would want to Group By your "PRODUCT NAME", select "RETAILER" as your New Column Headers, and "PRODUCT NAME" as your Values for New Columns.

This will result in your input data....

"Pivoting" into the following format:

The First "PRODUCT NAME" field is a result of the grouping process in the cross tab tool and could easily be turned off with a select tool.

Thanks!

[alex_reevoo]

Hi Mike,

Perfect that's almost got it.

Suppose now that the output product names are different for both retailers. This has resulted in certain rows being blank for Retailer 1, but populated with data for retailer 2.

How do I remove these blanks, so that I only product an output of 5 rows, rather than 10?

Alex

[MikeA]

Hi Alex,

For this one you will want to make use of the multi-row formula tool to create a new ID that will link up Product 1 from Retailer 1 with Product 6 from Retailer 2 for example. Once this ID is created you can use the cross tab tool to pivot the data into the desired format. Check out the attached workflow for an example of this process.

Thanks!

And Thank you to @MargaritaW for the assist on this one!

[alex_reevoo]

Hi Mike,

Hate to be a pain, our company is  using an older version of Alteryx (10.1.6) - are you able to reupload in that format?

Cheers and thanks for continued support!

[MarqueeCrew · Accepted solution]

If you open the yxmd file using notepad, you can edit the header from 10.5 to 10.0 and be able to use it.

[alex_reevoo]

Thank you!!!