Bug in Trim() function?

[daviewales]

I have an Excel spreadsheet (attached) which contains a single field.

Test Field

A

A

I want to delete any whitespace which occurs at the start or end of each row. For example, one row has two spaces inserted at the end.

My understanding is that the Trim() function should delete any leading or trailing whitespace.

Consider the following load script:

LOAD
    [Test Field],
    Trim([Test Field]) as [trim_Test Field]
FROM [lib://AttachedFiles/Test Qlik Trim.xlsx]
(ooxml, embedded labels, table is Sheet1);

My expectation is that [trim_Test Field] should not have leading or trailing spaces.

However, as can be seen from the image below, the Trim() function appears to do nothing at all:

Is this a bug?

You will find the exact Excel spreadsheet used in this example attached.

[micheledenardi · Accepted solution]

Trim() function removes only leading spaces (ascii code 32).

Your string, in ascii terms (dec), is composed as: 65+32+255

At the end of your string you have one non-breaking space  (NBSP, ascii code 255) character which is not a real space so Trim() function will not remove it.

To remove NBSP you have to use replace() or keepchar() functions.

[daviewales]

Thanks you. You are exactly right!

I originally tried to determine if this was the issue by pasting the text into PowerShell and using Python's 'ord()' function.

However, I have discovered that PowerShell automatically converts the non-breaking space to a regular space, which prevented me from finding the problem! 😞

Pasting the text into HxD or Frhed correctly identifies the problem, as does exporting the file as csv, and running it through Python's 'ord()' function. For example, I used this Python code:

 with open('Test Qlik Trim.csv') as file:
     for line in file:
         for char in line:
             print(char, ord(char))

One could also of course use Qlik's 'Ord()' and 'Right()' functions:

I wonder if the Qlik developers would consider adding a 'Strip()' function to remove ALL types of leading and trailing whitespace, much like Python's 'str.strip()' method.

Also, how did you do determine the ascii code was 255? So far as I can tell, it is 160.

[Anil_Babu_Samineni]

@Clever_Anjos  Check, If you can help?

[micheledenardi]

Both 255 and 160 are right, because ascii characters over 127 are specific to a code page, so they depends from the encoding you're using.

Code page 437 (DOS, Win 10) the ascii code 255 corresponds to NBSP .

Using Windows Unicode (Windows-1252 or UTF-16-LE) code page NBSP is 160.

[Clever_Anjos]

Is it fixed, right?

[daviewales]

@Clever_AnjosI suspect @Anil_Babu_Samineni may have been referring to my comment:

'I wonder if the Qlik developers would consider adding a 'Strip()' function to remove ALL types of leading and trailing whitespace, much like Python's 'str.strip()' method.'

[Clever_Anjos]

Please register your idea here: https://community.qlik.com/t5/Qlik-Sense-Enterprise-Ideas/idb-p/qlik-sense-enterprise-ideas

[daviewales]

Thanks @Clever_Anjos,

I've added a suggestion here.