Records Values of Rows to be updated as column header and values to be populated

Question

Hello Alteryx users - I have an excel file input which has 5 columns as Record ID , Earning or Deduction , Name , Earning Deduction Amount and Value and I wanna rearrange the output as for each Earning or deduction (row) to be updated as column and each Earning or deduction amount (row) to be updated as column and value to be populated .

Attached file has the input and output requirement. Any help with the workflow is highly appreciated

Alteryx Rows to columns.xlsx

CathyS_Slalom · Answer

Hi @JJ747 , you can use auto field tool to automatically set the field type for string columns.

Or you can dynamic select to select all the columns that contains EA, then you can select use SHIFT with the left cursor to highlight all fields that you want and then click OPTIONs change field type to the data type of your choosing.

JJ747 · Answer

Thanks so much for the workflow.

One quick question so we would wanna still keep EA as numeric and ER columns as String. So now with this both EA and ER fields gets to be updated as String . Any pointers how I could separate them as numeric and string columns

binu_acs · Answer

@JJ747 Another way of doing this with the tile tool, almost similar to @Qiu solution

Transpose_25_06.yxmd