"Translate" Expression not working in Feature Labels

[meldarzi]

The shapefile that I am connected to has object data "SYSTEMTYPE" = Abandoned, Existing, ProposedReplace, etc. I am trying to use the translate feature to return an alternate text, but the results are rather bizarre. The text return often contains symbols and is not part of the intended set. Is there a workaround or another way to do this?

[ChicagoLooper]

Hi @meldarzi 

1. VERIFY AND MAKE SURE YOU'RE TRANSLATING THE CORRECT ATTRIBUTE

We must first confirm the column heading is actually named SYSTEMTYPE and the values in that column are Abandoned, Existing, ProposedReplace, etc.

Open the 'Table View' of the shapefile you're connected to.

Upload a screenshot of the SYSTEMTYPE column from the Table View.

2. DIAGNOSE THE VALIDITY OF YOUR TRANSLATE EXPRESSION

Upload a screenshot of the  Modify Query window where you are building (writing) your query.

Before you take that screenshot, hit the Validate button next to check symbol in the lower left corner of the Modify Query window so error messages will be captured in the screenshot.

[O_Eckmann · Accepted solution]

Hi @meldarzi ,

If I've well understood the usage of translate :

With Translate (FIELDNAME, 'A', '_'), it replaces all letters A found in the field value by a _

With Translate (FIELDNAME, 'AB', '_+'), it replaces all letters A (1st character of 1st parameter) found in the field value by a _ (1st character of 2nd parameter) AND all letters B (2nd character of 1st parameter) by a + (2nd character of 2nd parameter)

With Translate (FIELDNAME, 'ABC', '_+'), it replaces all letters A (1st character of 1st parameter) found in the field value by a _ (1st character of 2nd parameter) AND all letters B (2nd character of 1st parameter) by a + (2nd character of 2nd parameter) AND letter C (3rd character of 1st parameter) by nothing (only 2 characters in 2nd parameter) So after the first 2 replacements, if other part of text string contains a letter C, all other characters are removed, and if it doesn't contain letter C, no other replacement are done.

In your case if you want to keep only 2 first letters, it's better to use 

 Upper ( Substr ( SYSTEMTYPE , 1, 2 ) )

to obtain AB for Abandoned, EX for Existing and PR for ProposeReplace

[meldarzi]

Thanks @ChicagoLooper, I've checked the following and everything looks to be good. The issue appears to be my use of the translate expression

[meldarzi]

Thanks @O_Eckmann, I suspected my use of the translate expression was at fault. I was assuming it would run as a simple "find and replace". Kind of confused why the expression is structured like this though.

[ChicagoLooper]

Screenshots showing your expression? Can you share two screenshots?

1. An image of your expression.

2. An image of the database file (dbf) showing the Table view.