I have a field called common_name. It is padded with spaces. What I am trying to do is create an additional field based off of Common_name that only houses unique names.
In the common_name field there are values like this:
My_Company
My_Company-DC3
A-To-Z Company
The it store-DC9
The comany names that end in -DC# are the duplicates. What I am trying to do is remove those characters from the end so I can group in the common_name.
I think basically if the 4th to last character is a hyphen remove the last 4 digits or something like that.
Thanks
Here is what I have so far but it simply returns CLLUnknownError. If I replace the 'Then' with ('BRET') it works (although marks them with my name) but I can seem to get the trimming from the right to work
if ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] contains '-DC')
then (substring ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] , 1 , len([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] - 3)))
else ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME])
Here is what I did to resolve the issue. Thanks to MEEP for a post I found from another user that helped me figure it out.
if ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] contains '-DC')
then (SUBSTR([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] ,1,CHAR_LENGTH([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME])-4))
else ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME])
Quote from: bpothier on 21 Jul 2014 08:54:13 AM
Here is what I did to resolve the issue. Thanks to MEEP for a post I found from another user that helped me figure it out.
if ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] contains '-DC')
then (SUBSTR([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME] ,1,CHAR_LENGTH([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME])-4))
else ([CRO_BV].[CRO_ACTUALS_TB].[COMMON_NAME])
Ah! Nice work! I always find it satisfying to figure out an answer by extrapolating facts from elsewhere. Thanks for posting up the solution - very much appreciated! :)
MF.