How to create number of columns based on one

[AYESHASIDIKHA]

Hi!

Greetings of the day,

I am facing one issue in my project since I cannot post the original data as it is confidential, I am posting some dummy data, but I want multiple columns to be generated based on 2021 expenses column.

Note: 2021 expenses are a field which has formula. I want the columns till 2030 expenses.

Input
Name	2021 Expenses
John	1000
Abraham	2000
Ravi	4000
Sujoy	488
Rani	6000
Mary	1130
Expected Output
Name	2021 Expenses	2022 Expenses	2023 Expenses	2024 Expenses	2025 Expenses	2026 Expenses
John	1000
Abraham	2000
Ravi	4000
Sujoy	488
Rani	6000
Mary	1130

Thanks in advance! 

[AYESHASIDIKHA]

Hi Susan,

Thank you, But I want the solution using alteryx 

[TheMattLeonard]

Hi - If I'm understanding the goal correctly, I think you will need a combination of Transpose, Generate Rows, and Crosstab to accomplish your goal.

1. First things first I'm going to refer to your Name column as "First Name" as the Transpose tool generates a field called "Name" and I don't want to mix them up.

2. Transpose your Input Data. First Name is going to be your Key Field and 2021 Expenses is going to be your Data Field.

3. Use a formula tool with the expression ToNumber(LEFT([Name],4)) to create a field called Year (Double)

4. Use a Generate Rows Tool. Update Existing Field "Year". Your Initialization Expression will be 2021. Your Condition Expression will be [Year] <= 2030. Your Loop Expression will be [Year]+1

5. Use a Formula Tool with the expression ToString([Year])+" Expenses" to rewrite the "Name" Field.

6. Use a CrossTab tool to pivot your data. Group on "First Name". Change Column Header to "Name". Values for New Columns should be "Value". Method for Aggregating your values should be "Sum"

This will just populate the 2021 values for all years but it will create the columns dynamically. If you need to adjustments to the numbers for future years that would go before the crosstab I would imagine.

Let me know if you have any questions.

Thanks