Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stevegpage1
Partner
Partner

Resolving an Excel Non-Breaking Space using SubField()

I am working with a large set of Excel files that I am importing into Qlik Sense. One of the fields that I need to import comes from a long text string. The string had a set of document numbers embedded in the string. Using the Index() and Mid() functions, I was able to locate and extract the document number. Since the document number could vary in length, I attempted to use the SubField() function to pull the document number out, by delimiting on the <space> that followed the document number. 

Example: SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1) AS DocumentNo

In most cases, this worked perfectly. However, in reviewing the results, I would get things like:

N011234XYZ12 01 - which still includes a space in the document number. After reviewing the source data, I discovered that in some cases, the data (which had initially been stored in SharePoint) included a "Non-Breaking Space"  (nbsp) (https://en.wikipedia.org/wiki/Non-breaking_space) .  The excel files where this comes from cannot be edited, so the  modification had to be done in the Qlik Sense script editor. 

After researching this issue, my solution was to add an additional SubField() function, by including the "invisible" nbsp. To do so, my resulting solution looked like:

SubField(SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1), ' ', 1) AS DocumentNo

Where in the second ' ' I typed <ALT+255>  (Hold the ALT key down, and type 255). The result returned just the document number, without the extra 'space' and characters - N011234XYZ12.

Hopefully, this will save someone some time if you come across this problem!

Senior Qlik Architect
Grey Matters Defense Solutions
Labels (1)
1 Solution

Accepted Solutions
stevegpage1
Partner
Partner
Author

Solution:

Add a SubField() function, by including the "invisible" nbsp. 

Example: SubField(SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1), ' ', 1) AS DocumentNo

Where in the second ' ' I typed <ALT+255>  (Hold the ALT key down, and type 255). 

Senior Qlik Architect
Grey Matters Defense Solutions

View solution in original post

1 Reply
stevegpage1
Partner
Partner
Author

Solution:

Add a SubField() function, by including the "invisible" nbsp. 

Example: SubField(SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1), ' ', 1) AS DocumentNo

Where in the second ' ' I typed <ALT+255>  (Hold the ALT key down, and type 255). 

Senior Qlik Architect
Grey Matters Defense Solutions

View solution in original post