Convert Field Name Type if Field's Name Meets a Certain Criteria

How can I convert any number of fields the names of which start with AB to a numeric field type (double) and all remaining field types to V_String (even if they contain numbers).

The catch here is that some fields contain numbers but need to be treated as strings. So, the Auto Field tool creates as many challenges as it solves.

In the example below all fields input as V_String. I want to convert the field type of all fields that start with AB to doubles.

Company	Committee	Product	AB Sword	AB Juno	AB Omaha	AB Gold	AB Utah	Status
Tesla	19	Cars	39	40	41	42	43	Green

Help

Accepted answers

BS_THE_ANALYST

@hellyars interesting challenge:
All should be in order. I did lose the column orders though, does that matter?

It's dynamic as per your request, I added in a fake row of data aswell

column_starts_with_change_data_type_else_string.yxmd

Thableaus

@hellyars

Use two Dynamic Select tools to split the fields by the field name rule (via Formula) (something like StartsWith([FieldName], "AB") and !StartsWith([FieldName], "AB") should work

With the "AB" Fields, use the Multi-Field Formula Tool to force the type change to Double.

With the others, use the Multi-Field Formula Tool to force it to String.

Join the dataset by Record Position if you are not using AMP.

If you are, maybe it's interesting to add a RecordID tool before the Dynamic Select tools and then join using RecordID.

Cheers,

All comments

BS_THE_ANALYST

@hellyars interesting challenge:
All should be in order. I did lose the column orders though, does that matter?

It's dynamic as per your request, I added in a fake row of data aswell

column_starts_with_change_data_type_else_string.yxmd

Thableaus

@hellyars

Use two Dynamic Select tools to split the fields by the field name rule (via Formula) (something like StartsWith([FieldName], "AB") and !StartsWith([FieldName], "AB") should work

With the "AB" Fields, use the Multi-Field Formula Tool to force the type change to Double.

With the others, use the Multi-Field Formula Tool to force it to String.

Join the dataset by Record Position if you are not using AMP.

If you are, maybe it's interesting to add a RecordID tool before the Dynamic Select tools and then join using RecordID.

Cheers,

hellyars

@BS_THE_ANALYST

Question first.... I have used the Dynamic Metadata tool to change the length of a field, do you know if it can also be used to change the type between string and numeric?

DataNath

Hey @hellyars, you can just create two streams based on Dynamic Selects of Startswith([Name],'AB') and not. For the positive stream, just change these all to doubles and then rejoin the 2 branches on record position:

Change AB fields to doubles.yxmd

BS_THE_ANALYST

@hellyars I've never heard of the Dynamic Metadata tool, resultantly I haven't used it 😣.

I like the sound of @Thableaus's suggestion.

hellyars

@BS_THE_ANALYST your solution is total brute force, but it works! The Dynamic Metadata tool is in the CReW Macros.

@Thableaus That was easy. Many thanks.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335