Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to handle special characters when applying cross tab to transpose field value to field name

jeeva_ganesan
8 - Asteroid
Hello all,

In my work, I am getting input data in key value format, which means one column having all the column names and the other ones having corresponding column values. Basically its a transposed version of the data. So I am applying Crosstab tool to get the data back to normal form. But the problem I am facing is - column names are having brackets, commas in it. For example, its like KILOS/LITRES('000). So when I apply transpose, column names are changed to this KILOS_LITRES___000_ . How to retain them in the original format?. 

I tried to use dynamic rename, but couldnt find a better logic to solve this as I have more than 40 columns in my data. 

Thanks in advance.
6 REPLIES 6
AdamR_AYX
Alteryx Alumni (Retired)

Hi,

Something like this should do what you need.  Not pretty, but gets the job done

rtaImage (4).png
So the summarise and RecordId tool create a lookup for each of your header fields names: 00001, 00002 etc...  The join tool replaces your original header field names with the new ones.  Which then go thorugh the cross tab without being changed.  Then the dynamic rename tool is in "Take field names from Right Input rows" mode which puts your original fieldnames back.

Module here

http://downloads.chaosreignswithin.com/community/KeepFieldNamesThroughCrossTab.yxmd

Cheers
Adam
 

Adam Riley
https://www.linkedin.com/in/adriley/
jeeva_ganesan
8 - Asteroid
Hi Adam,

Thanks for your reply. It worked very well. :)

Regards,
Jeeva.
BenMoss
ACE Emeritus
ACE Emeritus

Just a note on this, i've now created a new tool and made it available on the public gallery. The 'Cross Tab Real' is designed to negate this issue.

 

https://gallery.alteryx.com/#!app/Cross-Tab-Real/5a9802e6f499c7166cab6a53

 

Ben

Gmedin1
5 - Atom

Hi @

 

 

 

 

8088
7 - Meteor

Hi @BenMoss 

 

The custom tool you describe sounds mighty interesting - the link doesn't work, so is there another way to get a copy ?  thx.

W1ll3m
5 - Atom

Hi Ben

 

It made my day to find your cross tab real tool, but unfortunately it seems to have an issue. It gives an error of missing the ship_mode field that relates to the example you posted on this macro.

 

Any suggestions on how I can get a working version / fix the current macro?

 

Thanks in advance

 

Willem

Labels