Dynamically changing column from V_String to Double

[wzhu717]

Hello, 

i have a problem and i need help, 

i have 2 rows and the name of these 2 rows can change everytime. so i am thinking about to use a dynamic rename and take field names from first row of data. but then the problem now is when i joined them back to the original data, those 2 rows i dynamic select are now changing the original data type from double to V_string. i can't use select tool because the name of those 2 rows are changing everytime. so my question is how can i change the name of those 2 rows beside changing the data type to V_string? thank you.

[BenMoss]

Hey!

Could you perhaps mock up the exact issue you are facing.

I'm thinking you can do something using the multi-field formula tool, but it's hard to tell exactly.

Ben

[danilang]

Hi @wzhu717 

I realize that you say 2 "rows" but do you mean that 2 columns need to be renamed?  The columns afffected in a Dynamic Rename tool when configured in "Take the field names from the first row of data" have to be strings to contain the destination field name.  The only exception is if the new field name is a number.  In this case the entire column can be a numeric field.

I can think of a couple of options here

1. Are these 2 columns always in the same place in the column list?  If so, you can use a Dynamic Select to rename the columns instead of the Dynamic Rename.  Use "Select via a Formula" and base your formula on the FieldNumber

2. Edit the first row of data so that your field names have some kind on identifier, "NEW_" + fieldname.  Use the dynamic rename and then a dynamic select to change the type of any field that starts with "NEW_".  Use a second Dynamic rename with a formula to remove the "NEW_" from your columns.

Dan

[jasperlch]

Hi @wzhu717 

If the names of the fields which need to be converted back to Double has a certain pattern, you could use the method below:

After the Dynamic Rename tool, add a column of RecordID.

Then split the dataset into 2 streams (Top and Bottom, see figure below and workflow attached)

On the Top Stream: Add a Dynamic Select tool to select the fields which should be of type Double via a formula (e.g. StartsWith([Name], "Field") or Name = "RecordID"). Do remember to keep Name = "RecordID" as you will need to this column later on. Then use a Multi-Field Formula to change field types of all fields to Double.

On the Bottom Stream: Add a Dynamic Select tool to select all other fields which type should stay intact, together with the RecordID field via a formula (e.g. !StartsWith([Name], "Field") or Name = "RecordID")

Then Join the Top and Bottom stream on the RecordID fields and deselect the 2 RecordID fields. Make sure you have *Unknown fields selected in the Join tool.

Hope this helps.

[wzhu717]

Hi Dan, 

my apologies, you are correct, i meant columns and row. 

okay, so at the end the output always the same. there are 8 columns but the name of the 4th and 8th columns are always changing. 

if i am using dynamic select, (and i have tried that method) and able to pick out those two columns, what formula would i need to use to covert those two columns from V_string to Double without messing out the name of the columns?

thank you,

[wzhu717]

Hi Jasperich, 

thanks for the advise, i have tried that as well. the problem for that is when i used the multi Field Formula, i would need to select Field 1 and Filed 2 and in my data those Filed 1 and Filed 2 are not always the same name. so for this to work i would need to go in and select the correct name everytime for this to run correctly.

[danilang · Accepted solution]

Hi @wzhu717 

Use a Dynamic Select with the following formula

[FieldNumber] in (4,8)

That  will select all your columns.  Follow this up with a Multi-field formula configured like this

Make sure "Dynamic or Unknown fields" and  "Change Output Type To" are selected and set the output type to what ever you need.  

In parallel with these, use a Dynamic Select to select the other 6 columns and join this with the columns 4 and 6 stream on Record Position 

Dan

[jasperlch · Accepted solution]

Hi @wzhu717 

In the Multi-Field formula, you could select ""Dynamic or Unknown Fields". This will select any new fields coming into the tool.

[wzhu717]

thank you, Dan. it works.

[wzhu717]

Thank you very much.