Take data from one of the columns and concatenate it with other columns

[msyosd]

Hi Alteryx experts

Is there any elegant way to take data from one of the columns and concatenate it with other columns as per the attached sample data?

[apathetichell]

yes - if you use a macro. the value in filename has to be passed into a macro and replace specific string  in dynamic rename. dynamic rename has to be configured to only affect the columns you are changing the names of. The configuration is something like "test_"+[_currentfield_] - where test_ is the string you are replacing in the action tool.

If you try the attached version - you'd hook your datastream into it - configure the dynamic rename in the macro with the fields you want to replace - and send the value you want to concatenate to the fieldnames as the mapped control parameter. You only want to send it in one time - so unique/summarize/etc to isolate the value.

[apathetichell]

I'll also include an alternative way using transpose - formula and dynamic rename...

Here you select the first entry. You transpose - keeping the field you want to add a key column. You then create a new column with the fieldnames you want (ie identify the columns you want to change the value of and tostring([fieldname])+"-"+[name]) then use dynamic rename to look for the original column name and replace with the new one.

[AbhilashR · Accepted solution]

Hi @msyosd, an alternate approach to achieve your result set could be to use a combination of Record ID, Transpose, and Crosstab tools to rename the column headers. Transpose gives us access to column headers which we can rename using a Formula tool downstream, which can transpose back using the Crosstab tool. I have included a proof of concept for your review. This approach may or may not work if you have a dynamic universe of column headers, so just a heads up.

Hope this helps.

[msyosd]

Hi @AbhilashR 

Thank you for your quick reply. Indeed, the combination of transpose and cross tab worked! Much appreciated!