topic Re: How can I remove non-printable characters from a cell in Qlikview in QlikView

How can I remove non-printable characters from a cell in Qlikview

jennytan — Wed, 25 Nov 2020 16:16:04 GMT

How can I remove non-printable characters from a cell which has 2 lines of data. I tried trim but it does not work

Re: How can I remove non-printable characters from a cell in Qlikview

marcus_sommer — Tue, 25 Aug 2020 13:38:24 GMT

If a char isn't fetched by the trim-function you could apply keepchar/purgechar() to include respectively to exclude all (un)wanted chars.

- Marcus

Re: How can I remove non-printable characters from a cell in Qlikview

jennytan — Wed, 26 Aug 2020 07:03:02 GMT

Hello Marcus,

Thank you. The purgechar helped to remove the unwanted characters caused by the 'enter key'. However, it also removed all empty space between words. Any suggestion to keep the spacing between words and at the same remove the unwanted characters.

I used purgechar(Address, ' ') as Address1

Result is blk315tAMPINESSTREET33#12-40singapore520315

Re: How can I remove non-printable characters from a cell in Qlikview

Brett_Bleess — Thu, 03 Sep 2020 15:31:53 GMT

@marcus_sommer Hey Marcus, apologies for the notification issues we seem to be having, just wanted to let you know about this one, as I suspect you will have something additional, I am not quite sure where to head with it.

Regards,
Brett

Re: How can I remove non-printable characters from a cell in Qlikview

marcus_sommer — Thu, 03 Sep 2020 15:45:06 GMT

If purgechar() with excluding just a normal space respectively chr(32) worked it should also work with trim(). Therefore I think that there is no extra special char which caused the two lines of data else there isn't enough space within the cell and so the content is just shown on multiple lines without including any line-break chars.

To make sure which chars are really there it's often helpful to copy & paste the content into an editor like notepad++.

- Marcus

Re: How can I remove non-printable characters from a cell in Qlikview

jennytan — Wed, 23 Sep 2020 01:38:35 GMT

Hello Marcus,

Thanks for the assistance. Now I used the trim function and it shows correctly both for csv and xls. However, when I copied the values to notepad, I noticed that it had the " both at the beginning and end of the field.

Please see sample attached.

Re: How can I remove non-printable characters from a cell in Qlikview

marcus_sommer — Wed, 23 Sep 2020 07:07:15 GMT

It looked like a quoting - means the wrapping of a fieldvalue with a certain char which shouldn't be included within the values itself to ensure that other tools could exactly differ between fieldvalues, field-delimiters and record-delimiters.

Quite usual for this quoting is the use of the double-quotes and of course you could see them within an editor. If there is no issue within your following tools / process-chain in loading/interpreting the data you don't need to care about it.

- Marcus

Re: How can I remove non-printable characters from a cell in Qlikview

jennytan — Wed, 23 Sep 2020 07:25:50 GMT

Marcus,

Thank you.