Convert one particular row to column

[geeman]

Hi,

I am sorry if similar question is answered before, but I couldn't  find one. This might be a very basic question but I am new to Alteryx. So my requirement is to convert the last row in the recordset to a column like below;

Name	JAN	FEB	MAR	JUN	JUL
Value1	123	133	111	455	121
Value2	465	456	111	333	65
Value3	123	133	111	55	45
Value4	123	133	455	444	45
Value5	123	133	111	333	45
Year	2017	2017	2017	2017	2017

Convert to;

Name	JAN	FEB	MAR	JUN	JUL	Year
Value1	123	133	111	455	121	2017
Value2	465	456	111	333	65	2017
Value3	123	133	111	55	45	2017
Value4	123	133	455	444	45	2017
Value5	123	133	111	333	45	2017

Thank you in advance.

[NicoleJohnson · Accepted solution]

Try the attached workflow (created in version 11.3).

NOTE: This will only work if ALL the data in that row is the same. Otherwise, we will need more information about how that year is supposed to be mapped to the various records/values.

1. Sample Last 1 Row to isolate the desired row

2. Pivot the data with a Transpose tool, then summarize to get the value that needs to get added to each record

3. Append the year to the data (after joining to the Last Row that was isolated in Step 1, then taking the outer join to exclude that last row from your final data set).

Hope that helps!

NJ

[geeman]

Hi @NicholeJohnson,

Thanks for your quick response!

yes, I am expecting the last row to be the current year so it would be same. Actually I am using the previous version of Alteryx so could not open your sample file, but your step by step explanation helped. Thank you again for that!

Best,

[PeterV · Accepted solution]

Hi,

Isn't the solution simpler than that? Wouldn't the following work:

1. Remove the last row from the table by filtering for Name != "Year".

2. Add a formula tool to add the desired last column using "DateTimeFormat(DateTimeNow(),"%Y")".

Better?

Peter

[geeman]

Hi @PeterV,

Thanks for your suggestion! Sure, that makes sense.. I have modified my workflow to make it more generic, similar to your solution.

Best,

G