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

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

MetaData.xlsx

Datasets

Input

Interface Tools

Data Investigation

Accepted answers

Paul_s_Moody

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!

All comments

FinnCharlton

Hi @Paul_s_Moody

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

Column Rename.yxmd

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/td-p/672616

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

Paul_s_Moody

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!

PARESH

Hi, what if I have different column name with below combination

"Spect - Institution - 44984 - Atlantic Ocean AIV-1 A, L.P."

After crosstab its generating underscore for space as well as hyphen and when I am trying to replace its not reformatting the way I need it and I have so many columns and it gets increased / decreased each time. Because after this I have to union with the previous data as I am doing sum of dynamic column vertically.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335