Restore column headers and data types after Transpose and Cross-tab tool changes

[Paul_s_Moody]

Hi,

In an earlier post "Refer to "generic" / "dynamic" multiple columns in a formula", I was helped to generate a workflow using a Transpose tool, Multi-Row tool and then a Cross tab tool to manipulate values using multiple columns.

In setting up my test case I used simple column headers and data types didn't matter!

However, adding the sample workflow into my main workflow "works" in so far as the data is updated however, there are 2 unexpected issues:

1 - Spaces and dashes in the column headers have all been converted into underscores, e.g. "Date of Birth - 1" has been changed to "Date_of_Birth___1"

2 - All of the columns have had their data types set to V_WString.

Is there an (easy) way to revert the column to their original names and data types?

I've attached an excel file showing the metadata before and after the changes.

There are more than 150 columns in the data so I don't want to have to manually change using a select tool and the workflow is a template so the names may differ in subsequent versions of the data sets.

I was hoping that I could use some kind of dynamic replace based on the data file prior to the Transpose/Cross-Tab section, but I can't seem to find the correct syntax.

Thanks in advance

[FinnCharlton]

Hi @Paul_s_Moody 

A combination of field info and dynamic rename should help you here:

[DavidSkaife]

Hi @Paul_s_Moody 

For part 2 you could always drop an Auto Field tool on after the transformations have finished - that should sort out your data type issue as well

[RobertOdera]

Hi, @Paul_s_Moody 

The Dynamic Replace tool is more about the values in the column versus the column name.

1. You could use Dynamic Rename --> Formula --> Replace single underscore with blank

ReplaceChar([_CurrentField_], "_", " "), and add another to Replace double underscores with a dash ReplaceChar([_CurrentField_], "__", "-")

2. You could take the original field names from Metadata

3. You could insert a Record ID + Transpose + Crosstab + Select Records or Dynamic Rename 

Can you share a sample data file at the point where you would like to automate versus manually updating via Select Tool?

Cheers!

[Paul_s_Moody]

Thank you for this solution!

It works for the restoring column names.

It's just a little frustrating that a "workaround" is needed due to a "mis-behaving" tool!

[Paul_s_Moody]

Hi,

Sorry, I can't share the data because of commercial sensitivity - it's real data!

I've also tried using the Auto Field tool after the "revert to original names", but fields are reduced to either String or V_String; it doesn't seem to be able to restore the Date or Double data types.

As far as I can see all I'm left with is to manually change all 157 fields back to the original data type using a select tool...

Thanks

[DavidSkaife]

Hi @Paul_s_Moody 

It's odd than the Auto Field tool isn't working, it's usually fairly accurate in turning string fields into the right formats. It may be worth picking a field that should be a number and investigating as to why, perhaps there is some data in there that is being mistaken for a string?

Anyway, other that manually changing the field types there is another option...but depending on your Alteryx experience it may not be 'easy' as such. Have a read through this thread which presents some solutions on dynamically setting data types - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamically-change-data-type-of-fields...

You may be able to adjust it to fit your use case!

[FinnCharlton]

Hi @Paul_s_Moody ,

You could try something like this:

Here I use the multi-field formula tool to dynamically change field types.

[Paul_s_Moody · Accepted solution]

Thanks to all of you for your help!

The steps I've added are shown in the picture below:

The 1st block deals with the multi-column problems (setting a value to 0 is the value in the next column isn't a "Y")

The 2nd block deals with restoring the data types that are lost in the Transpose/Cross-Tab block, luckily I've only got 2 non-string data types so the split and re-merge isn't too complicated.

The link to https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamically-change-data-type-of-fields... was very useful as I was able to combine a number of the approaches discused.

It's a bit crazy when the equivalent code in Excel would be adding a simple formula!  =IF($A$10="Y", A$9$ , 0)

Thanks again!