Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
daviewales
Contributor III
Contributor III

Bug in Trim() function?

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

Test Field
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:

Trim Bug.png

Is this a bug?

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

Labels (2)
1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

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.

View solution in original post

8 Replies
micheledenardi
Specialist II
Specialist II

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.

View solution in original post

daviewales
Contributor III
Contributor III
Author

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:

Qlik Ord.png

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
micheledenardi
Specialist II
Specialist II

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
Employee
Employee

Is it fixed, right?

daviewales
Contributor III
Contributor III
Author

@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
Employee
Employee

daviewales
Contributor III
Contributor III
Author

Thanks @Clever_Anjos,

I've added a suggestion here.