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.
Solved! Go to 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
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,
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
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