Merging columns with suffixes into a column without

[Paul_s_Moody]

Hi,

I am combining data from a pension scheme and am hoping there is a way to avoid "manually" typing lots of data into a formula tool.

In the data set many of the columns need to be combined into a single column as they have the same features, but some times there is only 1 instance of the feature.

I've used a dynamic rename tool to change the input column name into a feature column name, but I would like to be able to combine the feature columns easily.

As an example a subset of the column names are:

Feature_A_1

Feature_A_2

Feature_B

Feature_C_1

Feature_C_2

Feature_C_3

I can solve this by typing into a Formula tool (adding new columns)

Feature_A = [Feature_A_1] + [Feature_A_2]

Feature_C = [Feature_C_1] + [Feature_C_2] + [Feature_C_3]

and then using a select tool to deselect the _1, _2 and _3 columns from the data.

I have more than 15 types of "Feature" and in some cases suffixes from _1 to _10.

Is there an easy way to to this? Or do I need to just type (well copy/paste out of Excel) row by row into the formula tool?

Thanks in advance

[binuacs · Accepted solution]

@Paul_s_Moody this can be achieved with the help of the transpose/cross tab tools. I am attaching a sample workflow for your reference. 

[Paul_s_Moody]

Many thanks!

The solution will work, but I'd then need to use additional workarounds to get the data back to its type and to reset column names containing spaces or dashes.

I've had similar issues when working with transpose and cross tab tools in combination.

Solved: Re: Restore column headers and data types after Tr... - Alteryx Community

Happy to add as solution as this will definitely work on simpler data files.

[binuacs]

@Paul_s_Moody This is the dynamic way to calculate the sum, also I created the summy input file based on your question. If your input pattern is different can you upload a sample input file and the expected output?