Hi, I was looking for a solution if its possible in Alteryx. Source data : FY 2015FY 2015 Cal. Year/monthJan-2015Jan-2015Col ACol BCol CCol DCol ENet SalesQuantity1XYZAAABCZ$887601XYZAAABCW$7,6751201XYZAAABCGHIZ$27151XYZAAABC GHKZ$4613 Required Output : Col ACol BCol CCol DCol E Cal. Year/month 1XYZAAABCZFY 2015Jan-2015Net Sales$8871XYZAAABCZFY 2015Jan-2015Quantity601XYZAAABCWFY 2015Jan-2015Net Sales$7,6751XYZAAABCWFY 2015Jan-2015Quantity120 Please let me know if a solution can be provided to it. Thanks Harsh

Data Preparation : Cross tab

Hi,

I was looking for a solution if its possible in Alteryx.

Source data :

					FY 2015	FY 2015
				Cal. Year/month	Jan-2015	Jan-2015
Col A	Col B	Col C	Col D	Col E	Net Sales	Quantity
1	XYZ	AA	ABC	Z	$887	60
1	XYZ	AA	ABC	W	$7,675	120
1	XYZ	AA	ABCGHI	Z	$27	15
1	XYZ	AA	ABC GHK	Z	$46	13

Required Output :

Col A	Col B	Col C	Col D	Col E		Cal. Year/month
1	XYZ	AA	ABC	Z	FY 2015	Jan-2015	Net Sales	$887
1	XYZ	AA	ABC	Z	FY 2015	Jan-2015	Quantity	60
1	XYZ	AA	ABC	W	FY 2015	Jan-2015	Net Sales	$7,675
1	XYZ	AA	ABC	W	FY 2015	Jan-2015	Quantity	120

Please let me know if a solution can be provided to it.

Thanks

Harsh

Output

Transformation

Preparation

Accepted answers

Joe_Mako

How about the attached workflow?

I built this with the assumption that there would be additional pairs of columns (Net Sales and Quantity) for each month, and the number of attribute fields (Col A - Col E) will not change. It will continue to work as more column pairs are added, but will need modification if the number of attribute fields changes.

It splits off the first two header rows from the data, then splits off the attribute fields from the pairs of columns, transforms each as needed, joins the streams back together, and renames the attribute fields.

Reshape

Reshape.yxmd

All comments

SophiaF

Hi @harsha384,

You will want to use the Transpose tool to acheive this. A couple prep steps will be needed:

1. You will need to create a column named "Cal.Year/month" with the appropriate month. This could be done dynamically in the workflow, or you can manually do this with a Formula tool.

2. You will use the Transpose tool to pivot the data. You will want to select the fields to "pull through" as your Key Fields, and fields you want to pivot as your "Data Fields"

Prepped data coming into the Transpose:

Results after Transpose:

Joe_Mako

How about the attached workflow?

Reshape

Reshape.yxmd

harsha384

Thanks for the solution. I have a another case :

INPUT is like :

				Curreny Year Profit				CURRENY YEAR SALE
Product	JAN	FEB	MARCH	APRIL	MAY	JUNE	JAN	FEB	MARCH	APRIL	MAY	JUNE
A	1	1	1	1	1	1	1	1	1	1	1	1
B	2	2	2	2	2	2	2	2	2	2	2	2
C	3	3	3	3	3	3	3	3	3	3	3	3
D	4	4	4	4	4	4	4	4	4	4	4	4
E	5	5	5	5	5	5	5	5	5	5	5	5
F	6	6	6	6	6	6	6	6	6	6	6	6

OUTPUT should be :

Product	KPI	Month	Values
A	Curreny Year Profit	JAN	1
B	Curreny Year Profit	JAN	2
C	Curreny Year Profit	JAN	3
D	Curreny Year Profit	JAN	4
E	Curreny Year Profit	JAN	5
F	Curreny Year Profit	JAN	6
A	Curreny Year Profit	FEB	1
B	Curreny Year Profit	FEB	2
C	Curreny Year Profit	FEB	3
D	Curreny Year Profit	FEB	4
E	Curreny Year Profit	FEB	5
F	Curreny Year Profit	FEB	6
A	CURRENY YEAR SALE	JAN	1
B	CURRENY YEAR SALE	JAN	2
C	CURRENY YEAR SALE	JAN	3
D	CURRENY YEAR SALE	JAN	4
E	CURRENY YEAR SALE	JAN	5
F	CURRENY YEAR SALE	JAN	6
A	CURRENY YEAR SALE	FEB	1
B	CURRENY YEAR SALE	FEB	2
C	CURRENY YEAR SALE	FEB	3
D	CURRENY YEAR SALE	FEB	4
E	CURRENY YEAR SALE	FEB	5
F	CURRENY YEAR SALE	FEB	6

Is it possible to solve it? Please guide!

Thanks and regards

Harsh

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703