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

[hellyars]

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

[BS_THE_ANALYST · Accepted solution]

@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

[Thableaus · Accepted solution]

@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:

[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.