How to create number of columns based on one

Question

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      Name2021 Expenses     John1000     Abraham2000     Ravi4000     Sujoy488     Rani6000     Mary1130            Expected Output      Name2021 Expenses2022 Expenses2023 Expenses2024 Expenses2025 Expenses2026 ExpensesJohn1000     Abraham2000     Ravi4000     Sujoy488     Rani6000     Mary1130

Thanks in advance!

TheMattLeonard · Answer

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