The selection of the fields dinamitically is difficult problem. Certainly, the Dynamic Select tool is a solution for the dynamic field select. But when there are too many fields, its work is a hassle. Or, you may think that it’s easy to use batch macro. However you can’t do anything to unknown fields when you rewrite the parameters in the Select tool by the Control Parameter tool. In this case, you can use the option “Update Value with Formula”, but it is a little bit complex to use.
I would like to explain more easy way to dynamic fields select in this post.
“Output Common Subset of Fields” option in the Union tool
The Union tool has a option “Output Common Subset of Fields”. The default setting of the Propaties is “Output All Fields” and normally you don’t need to change this option, so you may not be aware of this option.
This option is to select only fields that are present in all input datastream. So, when you have specific fields that you want, you just put the header data that you want to the Union tool with other data. In this case, I recommend to set the option “Ignore – Continue Processing Records” to the “When Fields differ”.
Sample workflow
The sample data is as follow. I want to change the position of the “UnitPrice” and the Description is not needed.
I define this as follows.
But I only use “Order” and “Name” at this time. I do nothing about Type and Size. In other words, I would like to sort the original data by Order number and only leave the fields in Name field.
According to this definition, I use the Cross Tab tool as follows.
- Change Column Headers : Order
- Values for New Columns : Name
After that, you can get the data below.
And then, you can use Dynamic Rename tool. You need to set the option “Take Field Names from first Row of Data” to the “Rename Mode”.
And also you need to check the all fields. The result is follows.
This data is what I want. You need to put them into the Union tool as follows.
The important point is that you have to connect the Dynamic Rename tool’s output anchor to the Union tool at first. And then, you can connect the data to the Union tool.
This means the field order and data type of the output of the Union tool will use the field order and data type in the first connected data stream. You can set the row order by the Output Order option, but the field order and data type are decided by the connected order.
You finally got the field order and fields that you want.
But there is a bad point. The fields type are changed as Text type. So that you need to change the correct field type. The easy way is to use the Auto Field tool. However if you want to change according to the definition, you need to do additional another way. But it’s another story.
Additionally, when there is Blob or SpatialObj type fields in incoming data, it will show the erros. The workaround is to change the Blob or Spatial field type to the appropriate field type after the Dynamic Rename tool.
In conclusion, this workflow can be used insted of the Select tool dinamically. But it is not able to apply for the Blob and Spatial type.
Conclusion
- You can use the Union tool with “Output Common Subset of Fields” option instead of the Select tool regarding to the field select.
- Note that all fields type are changed into text type. To address this issue, change the data type in header data stream or after the union, you can change the data type.
- When there are Blob type fields or SpatialObj type fields, it shows error. So you have to change the data type in header data stream. Note that the Blob Type is not shown in the menu, so you need to change in XML View directly.
- In this case, the table definition is in Text Input tool, however in the real situation, users may prefer those definitions in Excel or CSV.
Sample Workflow Download
Contents of the next blog
The next post will be about Spatial tips.
コメント