This post is about how to change the data type dynamically. But the case what I handle on this post is about a special case. That is you want to change the data type from unspecified fields which have specific fields type to specific data type.
Dynamic Data Type Conversion and Auto Field tool
What do you imagine about dynamic data type conversion? I think that you will use the Auto Field tool. That tool can change into the appropriate data type.
However, the data size is truncated to the minimum size that can be stored. But if there is no extra size a little, you sometimes fail the text handling. For example, when you want to add a prefix to a string field, you may see that there is no change. In this case, you need to change the data field size in advance.
Batch macro using the Select tool
So, I think you try to use batch macro using the Select tool. But this way is quite difficult. When you try to use that, you will face many problem. For example, when you connect to the Select tool which you change nothing from Action tool, you will know that you can’t change anything. The Action tool can only change something changed now.
But for the case like this, you can use the option “Update Raw XML with Formula” on the Action tool. But this way is difficult. First, you need to know what is XML. I think that if you see the XML view, you can understand the XML. The way to see the XML view is to check the “Display XML in propertoes Window” option in the “Options” – “User Settings” – “Edit User Settings” – “General”.
But this way is not easy, let’s go another way.
Dynamic Data Type conversion by Multi-Field Formula tool
I introduced this way at the latest post.
You can change the data type dynamically by the Multi-Field Formula tool. The important point is to use the “Dynamic or Unknown Fields” option for the selected data type.
In other words, when you select “Numeric” on the “Select Fields” option and check the “Dynamic or Unknown Fields” option, it will ignore the not numeric unknown fields.
You can select one type from the list below on “Select Fields” option.
- Numeric
- Text
- SpatialObj
- Bool
- Date Time
- Time
- All Type of
Example
This is the sample workflow.
Workflow :
The setting of the Multi-Field Formula :
Input :
Output :
Note that the spatial type is actually GeoJSON data. So, when it is converted to string type, it will be shown GeoJSON data.
Control the convert more finely with the Dynamic Select tool
If you use the Multi-Field Formula tool with Dynamic Select tool, you can control convert more finely. For example, when you want to change the Double type fields to fixed decimal type, you can use select the double type fields by the Dynamic Select tool and change them to fixed decimal type. On other hands, you can join another fields by record position. This workflow is as follows.
The important point is to select the double type fields by the Dynamic Select tool.
Next, change the data type by the Multi-Field Formula tool.
At the another data stream, select the not double type fields.
And then, join two data stream by record position.
By the way, if there is the data processing which change the record position before join with the not double type fields, it takes a little ingenuity. In this case, you add the record position identifier and join by it as key field.
In this case, the setting of the Dynamic Select tool is a little complex because you need to include the record position identifier.
IF [Type]="Double" THEN "True"
ELSEIF [Name]="_RecordPos_" THEN "True"
ELSE "False" ENDIFBut in this case, you need to exclude the “_RecordPos_” field on the Multi-Field Formula for the target of the data type convert.
Set the text size for all fields to 255
By the way, let’s talk about another situation. I explained that the Auto Field tool changes into the minimum size on the text type fields at first of this post. But it is not intuitive. So, I would like to explain to change the data size to 255 in bulk. This is that workflow.
It is very simple. Use the “Change Output Type to” and specify the size. If you want to know the detail, please see the sample workflow.
Conclusion
- I introduced how to change the data type dynamically at the Alteryx
- Basically, you can use the Multi-Field Formula tool. And if you use the Dynamic Select tool with it, you can control more finely.
- This technique is needed for the generic macro
Sample Workflow Download
The Next Blog Post is…
It will be about “Summarize” category tools tips.
コメント