I am trying to export data from SQL to CMDM Geodatabase. 200 values got truncated. I changed some of the attribute widths in CMDM, and truncated values decreased to 4. Instead of trying to check each attribute manually, is there any way to find these values directly?
Solved
How to find truncated values
+5- Contributor
Best answer by gio
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.
+45- Evangelist
Hi @canerakin111,
Are you use the Reader SQL or the transformer SQLCreator?
Thanks,
Danilo
+17- Contributor
Have you tried using the StringLengthCalculator transformer to calculate the required length of the attributes?
+5- Contributor
danilo_fme wrote:
Hi @canerakin111,
Are you use the Reader SQL or the transformer SQLCreator?
Thanks,
Danilo
I am using the Reader SQL (MSSQL Spatial)
+5- Contributor
canerakin wrote:
I am using the Reader SQL (MSSQL Spatial)
+5- Contributor
erik_jan wrote:
Have you tried using the StringLengthCalculator transformer to calculate the required length of the attributes?
+33- Influencer
You can use the attributevalidator to check for string length and can test this across multiple attributes
+15- Contributor
- Best Answer
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.+45- Evangelist
canerakin wrote:
I tried it right now, but I have more than 20 different attribute columns. StringLengthCalculator can check only one. maybe I need to write a python script to make a loop to check every attribute. This is going to be so complex though
Danilo
+17- Contributor
canerakin wrote:
I tried it right now, but I have more than 20 different attribute columns. StringLengthCalculator can check only one. maybe I need to write a python script to make a loop to check every attribute. This is going to be so complex though
Select max(length(attr1)) as max_attr1, max(length(attr2) as max_attr2 etc from table;
That would return all maximum lengths for the attributes.
+15- Contributor
gio wrote:
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.
+15- Contributor
gio wrote:
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.here is a pic (I also filtered oracle% as i used data from a oracle database)
+8- Contributor
gio wrote:
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.I think this is the best way of doing it, particularly if you want to find out the maximum length of the values in each field, for which I added Group By = _attr_name to the StatisticsCalculator. I needed to do this after getting the warning "257 text values were truncated during write" in a translation. Which values in which fields? Now I can compare the Inspector results with my Writer and update the field lengths accordingly.
+8- Contributor
+26- Evangelist
gio wrote:
@canerakin111
If you want to know longest attribute value of a set of attributes.
Attribute Exploder (default setting) followed by a attribute creator using the arithmetic editor :
New Attribute = @StringLength(@Value(_attr_value))
Then StatisticsCalculator max on that value (no group by attribute required).
If you only require this max you can use Summary output from the
Statistics Calculator
.I found this solution quite useful for same problem.
A way to speed it up further though potentially is I replaced the StatisticsCalculator, which can be quite slow with large datasets instead to something that should execute much faster:
- Send the output of the String Length AttributeCreator to Sorter->Sort by _attr_name (Ascending), StrLength (Numeric Descending)
- Then pass that into DuplicateFilter->Key Attribute = _attr_name with "Input is Ordered" = "Yes"
This is kind of like the "SELECT TOP...ORDER BY..." or "SELECT .... ORDER BY... LIMIT 1" SQL methodologies commonly used to solve this problem to find the highest or lowest value in a group in situations where using GROUP BY becomes too expensive/slow to execute: You order/sort the results by the highest value first within each Key value group to make it the "Top/First" result, and then limit the selection to the first item in each group which is essentially what a Sorter+DuplicateFilter combination does. Similarly, both Sorter and DuplicateFilter are Transformers that execute very fast in the Workspace.
+8- Contributor
bwn wrote:
I found this solution quite useful for same problem.
A way to speed it up further though potentially is I replaced the StatisticsCalculator, which can be quite slow with large datasets instead to something that should execute much faster:
- Send the output of the String Length AttributeCreator to Sorter->Sort by _attr_name (Ascending), StrLength (Numeric Descending)
- Then pass that into DuplicateFilter->Key Attribute = _attr_name with "Input is Ordered" = "Yes"
This is kind of like the "SELECT TOP...ORDER BY..." or "SELECT .... ORDER BY... LIMIT 1" SQL methodologies commonly used to solve this problem to find the highest or lowest value in a group in situations where using GROUP BY becomes too expensive/slow to execute: You order/sort the results by the highest value first within each Key value group to make it the "Top/First" result, and then limit the selection to the first item in each group which is essentially what a Sorter+DuplicateFilter combination does. Similarly, both Sorter and DuplicateFilter are Transformers that execute very fast in the Workspace.
Like it! I'll try that next time I need to do this.
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.