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

[JJ747]

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

[CathyS_Slalom]

Hi @JJ747 , please see the attached sample workflow.

[Qiu]

@JJ747 
We can also do a Transpose then with a Multi-row formula to processing for data and header as below.

[binuacs]

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

[JJ747]

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

[CathyS_Slalom]

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.