Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert one particular row to column

geeman
8 - Asteroid

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;

 

NameJANFEBMARJUNJUL
Value1123133111455121
Value246545611133365
Value31231331115545
Value412313345544445
Value512313311133345
Year20172017201720172017

 

Convert to;

 

NameJANFEBMARJUNJULYear
Value11231331114551212017
Value2465456111333652017
Value312313311155452017
Value4123133455444452017
Value5123133111333452017

 

Thank you in advance.

 

4 REPLIES 4
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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
8 - Asteroid

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
8 - Asteroid

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

Labels