Transpose tool issue with selecting key Columns

Hi,

I have prepared a workflow to clean data present in an excel file in different sheets. I am facing issue with transpose tool. I want to select 1st three columns as key columns in transpose tool (used in my workflow). However, I do not know why every time, I run this workflow or I used it for my 2nd sheet, chosen 3 columns in transpose tool gets de-selected and again, I have to choose it.

I want to know the reason why it is happening with my workflow. And, how can I fix 1st three columns as key fields in transpose tool, so whenever I run it for present sheets in the workflow (sheet1 or sheet2), my 3 columns do not get transposed.

Please help me with this!

Thanks!

Key Field of Transpose tool.yxzp

Workflow

Accepted answers

atcodedog05

Hi @Rahul_Analyst

Use formula like this

Hope this helps : )

Key Field of Transpose tool.yxmd

RolandSchubert

Hi @Rahul_Analyst ,

my approach would be:

I split input data in header and data rows, r´transpose both and use a Find Replace tool to set the field names.

Let me know if it works for you.

Best,

Roland

Key Field of Transpose tool 2.yxmd

All comments

gabrielvilella

Unfortunately that will happen because you have a dynamic rename before the transpose. Alteryx does not maintain the metadata after the dynamic rename as it does not know what could be the field names unless it runs. One option would be having the transpose inside a macro, where you send the field names to be selected from outside the macro. I also have seen cases where if you insert the dynamic rename into a macro and enable the option Output field changes based on macro data input in the interface designer it might work.

Rahul_Analyst

@gabrielvilella ,

Thank you for your response.

Can you please give me one workflow as an example ? It will help me understand it better and I will be able to make changes in my workflow.

Regards,

Rahul

binu_acs

@Rahul_Analyst use a select tool after the dynamic rename tool then connect to the Transpose tool, this will solve your issue. I think even though the dynamic rename changes value but passing the original value to the next tool, thats why the Transpose tool was not able to recognize the fields and deselecting each time

Rahul_Analyst

@binu_acs,

It is not giving me the correct output. I have put select tool before the transpose. I have chosen 1st three columns as a key columns but in output of transpose taking no field as a key columns and transposing each column.

Screenshot (5).png

Emil_Kos

Hi @Rahul_Analyst,

Could you share your current workflow?

Rahul_Analyst

@Emil_Kos Hi,

Here you go!

Key Field of Transpose tool_V1.yxzp

Emil_Kos

Hi,

I am not sure if I know that is the expected output but what I would recommend is to keep the odd naming convention of the columns keep it F1 F2 F3 etc and correct it after the transpose tool.

Union tool at the end will allow you to correct the naming convention:

Key Field of Transpose tool_V1- Data Pal.yxzp

atcodedog05

Rahul_Analyst

@atcodedog05 ,

Applying your idea, transpose tool is now retaining and keeping 1st three columns as key columns. Great!

Can you help me change regex match formula expression in my workflow so in Name columns, the value will be copied down where ever it is containing value (feild_digit_anything) ?

Rahul_Analyst

@Emil_Kos , it did not give me the desired output. However, it gave me a new way to trick Alteryx for getting my desired outcome. Once again, thanks for your time and effort!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703