topic Re: - Underscores After - Cross Tab in Alteryx Designer Desktop Discussions

- Underscores After - Cross Tab

suby — Mon, 04 Jul 2022 08:42:08 GMT

Hi All,

I have searched the community already and there where more solutions but looking for a solution on my specific requirement

I'm doing the crosstab tool function within a macro and then outputting the data from my macro to overwrite the input file.

The input columns looks like these..

Customer Name - Contact

Customer Name Contact: Contact-Address

Customer - Age

But After crosstab

Customer_Name_Contact

Custome_Name_Contact_Contact_Address.

Customer_Age

I'm looking for a solution where i don't want to rename the columns using the select tool since i have a huge list of Columns rather a dynamic way so that the name matches exactly the same as Input Data Columns.

Thanks

Re: - Underscores After - Cross Tab

IraWatt — Mon, 04 Jul 2022 08:47:02 GMT

Hey @suby,

You can use the dynamic rename, select all columns and use this rename formula:

Re: - Underscores After - Cross Tab

messi007 — Mon, 04 Jul 2022 08:47:31 GMT

@suby,

You can use a dynamic rename and replace function.

Attached the workflow,

Regards

Re: - Underscores After - Cross Tab

suby — Mon, 04 Jul 2022 09:26:40 GMT

Hi,

Thanks for the solution but when i have column header like this

Customer Name Contact: Contact-Address

if i use the replace function I loose this semicolon and a hyphen as well any thoughts how to fix this..

Re: - Underscores After - Cross Tab

IraWatt — Mon, 04 Jul 2022 09:30:11 GMT

I had a try with Customer Name Contact: Contact-Address and it didnt remove the semicolon and a hyphen. You could untick that column from the top if your having issues. Or we could make the formula more complex.

Re: - Underscores After - Cross Tab

DataNath — Mon, 04 Jul 2022 09:41:14 GMT

How does this look @suby? Instead of hard-coding replacements, this may be a little more dynamic. The underscores basically come from Alteryx replacing 'special characters' like spaces and punctuation with _ when Cross-tabbing. One of the options of the Dynamic rename tool is that we can provide a header to look for and what to replace it with. Therefore, this Formula tool off-shoot basically replaces anything that isn't a word in the original header with _ and so should create a copy of what the Crosstab tool will output, meaning we can just use that as a reference to find and replace it with the original header:

EDIT: Forgot to mention. This also means that if there does happen to be underscores in your desired headers, these 'innocent' underscores won't be removed.

Re: - Underscores After - Cross Tab

binu_acs — Mon, 04 Jul 2022 09:41:17 GMT

@suby one way of dong this by only taking the column names then using dynamic rename tool replace the cross tab columns, this way you can keep the original column names