Hi, I have a table which has identical data in some fields for a bunch of records. I am currently grouping on these fields, but these fields are repeated for each record. In Excel, when I create a Pivot Table using the following data: NameDeptProductPriceJohnSalesA500JohnSalesB750JohnSalesC1000JohnSalesD1250JohnSalesE1500JohnSalesF1750MattSalesA500MattSalesG2000 I can make it look like this using a Pivot Table: Is there a way to show such a piece of data in the above form using Alteryx? Thanks

Group identical data in columns like an Excel Pivot table

Hi,

I have a table which has identical data in some fields for a bunch of records. I am currently grouping on these fields, but these fields are repeated for each record.

In Excel, when I create a Pivot Table using the following data:

Name	Dept	Product	Price
John	Sales	A	500
John	Sales	B	750
John	Sales	C	1000
John	Sales	D	1250
John	Sales	E	1500
John	Sales	F	1750
Matt	Sales	A	500
Matt	Sales	G	2000

I can make it look like this using a Pivot Table:

Is there a way to show such a piece of data in the above form using Alteryx?

Thanks

Output

Preparation

Accepted answers

patrick_digan

@kunalhdoshi Nothing really slick was coming to mind, so I'm just manually adding another If statement on the Dept and Product columns to check if the columns to the left (which I'm just manually providing those columns) are null.

Crosstab1 (1).yxmd

All comments

patrick_digan

@kunalhdoshi Perhaps @pcatterson's crosstab solution to this similar problem will work?

kunalhdoshi

This comes pretty close to what I am looking for. It takes every column separately and in my example it still retains the Dept name 'Sales' even though it is the same as the set of records above it.

The workflow you mentioned will remove the Sales for the second set of records as it is the same for the set above it. Every new set of records which should be treated hierarchically from left to right and if a new value shows up to the left, then all the subsequent columns should show their values again even though they are identical to the set of records above it.

This is what it looks like with the workflow you mentioned:

I wish it showed the Sales again for the value Matt.

Thanks for your reply, it helped me to a certain extent.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335