Cross tab - not able to return hyphens as a part of field names

[sandeep_chayanam]

Hi all,

I was wondering if any of you have come accross the below bug / unexpected behaviour with the cross tab tool?

I tried to cross tab file names from the below list. the below highlighted file names are pivoted incorrectly. i.e., column names contain '_' (underscore) instead of '-' (hyphen). Please find it attached workflow for example.

File Names	GroupingField
File1_A	Dummy
File1_A-B	Dummy
File2_A	Dummy
File2_A-B	Dummy

I can guess that it could be because of restrictions in column naming convention,  but atleast I was hoping to see as-is values as column names coming out of Cross tab tool.

P.s, I managed to get as-is names using dynamic-rename/select tool.

Appreciate your views on this.

Thanks,

Sandeep.

[RodL · Accepted solution]

Sandeep,

Not sure the reason for it (our developers from long ago might be able to provide that)...I suspect it is trying to be "SQL compliant" with naming of fields.

It IS expected behavior, and the use of the Dynamic Rename with a formula to replace the underscore with a hyphen is the best approach to get what you want.

Rod

[sandeep_chayanam]

Thanks for your quick response Rod.

It would be interesting to know the actual logic for this automatic column names translation from the developers so that I can possibly create a macro to return as-is values as the cross tab o/p (as an alternative to my other approach mentioned below).

Regards,

Sandeep.

[Ned]

>It would be interesting to know the actual logic for this automatic column names translation from the developers...

for (wchar_t * p = strOutputFieldName.Lock(); *p; ++p)
{
	if (!iswalnum(*p))
		*p = '_';
}
strOutputFieldName.Unlock();

[sandeep_chayanam]

Thanks Ned.

[NeilR]

So (to roughly translate the code Ned provided) the Cross Tab tool converts all non-alphanumeric characters to underscores for the Header Field. This became an issue for me recently when I was trying to create a dataset which represented the Tableau field types for a given Alteryx dataset. The solution I came up with involved a fairly simple regular expression...

[^a-zA-Z\d]

... and the workflow attached (and screenshotted below)...