Usage converted text to numeric
5 August, 2014
In a database of my custome text's are used to store data that in fact is numeric. I want tot use this data in a filter and having the ability to select between a minimum (equal or greater) and maximum (equal or less) value. For this purpose i use the built-in Text to numeric converter. Hower in the report and in the filter-settings the result of this conversion doesn't behave as a numeric (i can't sum and i can't use numeric evaluation functions in my filter-settings). Is there some kind of limitation on this conversion-function?
Hi (is it you Peter?),
If for whatever reason the converter isn't working then I would use a custom function to convert this value to an integer, for example here is one that I added to the bottom of my current Custom-Functions.xml file (appserverwebappsROOTWEB-INF):
[code]
CastToInteger
1
numeric text
text
cast($1 as int)
SQLServer
numeric
[/code]
Then if you are not familiar with how to use Custom Functions, all you have to do after adding this code and restarting YF is to set a Calculated Field to Predefined Formula and choose it. I have tested it and it works, you may have to adjust it according to the CAST function of your own particular DB (this example is for SQL Server).
Another option would be to cast the numeric text to an integer in a virtual table.
(Unfortunately the Freehand SQL Calculated field in the View Builder can't be used for this purpose because it is not allowed to be used as a filter in a report, that's why the 2 previous methods have been advised.)
I hope that helps, any questions please let us know.
regards,
Dave
If for whatever reason the converter isn't working then I would use a custom function to convert this value to an integer, for example here is one that I added to the bottom of my current Custom-Functions.xml file (
[code]
cast($1 as int)
[/code]
Then if you are not familiar with how to use Custom Functions, all you have to do after adding this code and restarting YF is to set a Calculated Field to Predefined Formula and choose it. I have tested it and it works, you may have to adjust it according to the CAST function of your own particular DB (this example is for SQL Server).
Another option would be to cast the numeric text to an integer in a virtual table.
(Unfortunately the Freehand SQL Calculated field in the View Builder can't be used for this purpose because it is not allowed to be used as a filter in a report, that's why the 2 previous methods have been advised.)
I hope that helps, any questions please let us know.
regards,
Dave
(yes, it's Peter, i didn't see i wasn't logged in when i posted the question, but it is a good thing that "guests" also can post on the forum and become an answer)
The workaround with the custom-function is a solution that i understand and proofed that can be used, but still doesn't make clear why the built-in Field-conversion doesn't work. Is it a bug or is this functionality limited to specific fieldtypes or purposes in the program?
In the last case, is there information how and where the built-in conversion can be used, or can you specify those limitations? It doesn't sound logic to build a custom function where a built-in should work....
The workaround with the custom-function is a solution that i understand and proofed that can be used, but still doesn't make clear why the built-in Field-conversion doesn't work. Is it a bug or is this functionality limited to specific fieldtypes or purposes in the program?
In the last case, is there information how and where the built-in conversion can be used, or can you specify those limitations? It doesn't sound logic to build a custom function where a built-in should work....
Hi Peter,
I do believe this is a defect and have raised a task (TASK ID = 169743) which will need to be fixed after the 7.1 release.
Until then it looks like the only way to achieve what you're after is using a freehand sql conversion.
Really sorry for the inconvenience this has caused.
Regards,
David
I do believe this is a defect and have raised a task (TASK ID = 169743) which will need to be fixed after the 7.1 release.
Until then it looks like the only way to achieve what you're after is using a freehand sql conversion.
Really sorry for the inconvenience this has caused.
Regards,
David
No problem, we're here to find it, solve it and use it.
And i'm sure your guys/ladies will fix it. The result will be a very strong function for the users that want to develop a "clear view" on a "dirty source" where such a data-typing is used in the past.
And i'm sure your guys/ladies will fix it. The result will be a very strong function for the users that want to develop a "clear view" on a "dirty source" where such a data-typing is used in the past.
Hi Peter,
Just letting you know I have received an update on this task 169743.
The view level 'text to numeric' converter will not actually change the data type of that field. So you will not be able to apply any aggregations to this field (e.g. SUM/AVG)
It will simply update the 'column format' settings of that field on the report level. Essentially , so you can add the decimal format option.
The only way to really convert a text to numeric field is to use a view level calc field that applies a CAST/CONVERT to that field.
Sorry for the amount it has taken to get this task looked into.
Regards,
David
Just letting you know I have received an update on this task 169743.
The view level 'text to numeric' converter will not actually change the data type of that field. So you will not be able to apply any aggregations to this field (e.g. SUM/AVG)
It will simply update the 'column format' settings of that field on the report level. Essentially , so you can add the decimal format option.
The only way to really convert a text to numeric field is to use a view level calc field that applies a CAST/CONVERT to that field.
Sorry for the amount it has taken to get this task looked into.
Regards,
David
Hi Dave
I have some understanding of SQL, but am not a techie. I have a similar issue as highlighted in this thread.
From one data source, I take a field that is in text format. From another source, I take a field with the same key matching data, but in integer format. As a result, I cannot join the fields in 'advanced subquery'. How can I convert text to integer or integer to text? Peter refers to a built-in text to numeric function, but I cannot see it. Could you elaborate?
Regards
Paul
I have some understanding of SQL, but am not a techie. I have a similar issue as highlighted in this thread.
From one data source, I take a field that is in text format. From another source, I take a field with the same key matching data, but in integer format. As a result, I cannot join the fields in 'advanced subquery'. How can I convert text to integer or integer to text? Peter refers to a built-in text to numeric function, but I cannot see it. Could you elaborate?
Regards
Paul
Hi Dave
This is Paul now logged in
Regards
This is Paul now logged in
Regards
Hi Dave
Working from the other direction, I was able to convert a text field to numeric. However, when I try to join the fields from the two sources, both of which are now numeric, I cannot because it appears the fields are still of different data types.
How can I get round the problem?
I am on v 7.1 20150123.
Thanks and regards
Paul
Working from the other direction, I was able to convert a text field to numeric. However, when I try to join the fields from the two sources, both of which are now numeric, I cannot because it appears the fields are still of different data types.
How can I get round the problem?
I am on v 7.1 20150123.
Thanks and regards
Paul
Hi Paul,
well, in theory that should have worked, maybe they are different numeric types, e.g. float and integer, I guess the yellowfin.log file might have the error message which will tell what the exact problem is, you can email it to us if you want and we'll have a look at it (please quote the subject of this post).
Otherwise, in answer to your previous question, to convert from integer to text you can just use CAST( as VARCHAR(20)) or some such function depending on the idiosyncrasies of the particular database you are using.
regards,
Dave
well, in theory that should have worked, maybe they are different numeric types, e.g. float and integer, I guess the yellowfin.log file might have the error message which will tell what the exact problem is, you can email it to us if you want and we'll have a look at it (please quote the subject of this post).
Otherwise, in answer to your previous question, to convert from integer to text you can just use CAST(
regards,
Dave
Hi Paul,
could you please try a Freehand SQL Calculated Field as shown in the image below, it should definitely do the trick:
Please let us know how it goes.
regards,
Dave
could you please try a Freehand SQL Calculated Field as shown in the image below, it should definitely do the trick:
Please let us know how it goes.
regards,
Dave
thanks Dave, I tried that but got the following error:
ah, my mistake, sorry about that, I didn't the MySQL documentation carefully enough:
(Extract from http://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#function_cast)
The type for the result can be one of the following values:
� BINARY[(N)]
� CHAR[(N)]
� DATE
� DATETIME
� DECIMAL[(M[,D])]
� SIGNED [INTEGER]
� TIME
� UNSIGNED [INTEGER]
�
In other words, for the CAST function you have to use either SIGNED or UNSIGNED rather than INTEGER
So this time could you please try CAST(TaxNumber AS SIGNED) and let us know how it goes
regards,
Dave
(Extract from http://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#function_cast)
The type for the result can be one of the following values:
� BINARY[(N)]
� CHAR[(N)]
� DATE
� DATETIME
� DECIMAL[(M[,D])]
� SIGNED [INTEGER]
� TIME
� UNSIGNED [INTEGER]
�
In other words, for the CAST function you have to use either SIGNED or UNSIGNED rather than INTEGER
So this time could you please try CAST(TaxNumber AS SIGNED) and let us know how it goes
regards,
Dave
Hi David,
I tried both SIGNED and UNSIGNED. The field name is correct.
Regards
Paul
I tried both SIGNED and UNSIGNED. The field name is correct.
Regards
Paul
Hi Paul,
Is that the correct table? In an earlier email you had highlighted employee_all rather than employee_history_all
regards,
Dave
Is that the correct table? In an earlier email you had highlighted employee_all rather than employee_history_all
regards,
Dave
Hi David
If it helps, I'll email you the latest log for yesterday.
Thanks and regards
Paul
If it helps, I'll email you the latest log for yesterday.
Thanks and regards
Paul
Hi Paul,
Thanks for the log, it�s definitely saying that TaxNumber doesn�t exist:
[code]
YF:2015-04-14 18:05:57:ERROR (DBAction:doSelect) - Error occured selecting data: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TaxNumber' in 'field list'
[/code]
Could you please try going back to step 1 of the View Builder, clicking the Properties link for the Employee_all table, and then selecting the Mandatory checkbox for that table:
regards,
Dave
Thanks for the log, it�s definitely saying that TaxNumber doesn�t exist:
[code]
YF:2015-04-14 18:05:57:ERROR (DBAction:doSelect) - Error occured selecting data: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TaxNumber' in 'field list'
[/code]
Could you please try going back to step 1 of the View Builder, clicking the Properties link for the Employee_all table, and then selecting the Mandatory checkbox for that table:
regards,
Dave
Hi David
I tried it and got another error message. Joins with other tables etc. etc
HOWEVER, I got rid of some superfluous tables in the view and HEY PRESTO, it worked!!
Thanks very much for sticking with me.
regards,
Paul
I tried it and got another error message. Joins with other tables etc. etc
HOWEVER, I got rid of some superfluous tables in the view and HEY PRESTO, it worked!!
Thanks very much for sticking with me.
regards,
Paul