topic Re: Anyone recognise these characters from Excel? in QlikView

Anyone recognise these characters from Excel?

Anonymous — Mon, 11 Jun 2018 19:46:08 GMT

Hi,

We've received some data from a client using a new finance system. It looks like some sort of encoding in the data (this is a screenshot from QlikView). The issue is we can't see the characters in the original XLSX file we received and it only appears in QlikView.

Thoughts on what they are and how to get rid of them appreciated?

I should add it's not all lines. Some don't have these chars.

Re: Anyone recognise these characters from Excel?

isingh30 — Mon, 11 Jun 2018 20:50:39 GMT

You want only middle numbers/code/name? If yes, use subfield or right/left function.

Re: Anyone recognise these characters from Excel?

marcus_sommer — Tue, 12 Jun 2018 10:29:26 GMT

You could quite easily detect what special chars are included (within the excel - that they aren't displayed there doesn't mean that they didn't exists there) by using something like this:

ord(left(YourField, 1))

ord(right(YourField, 1))

and with this knowledge you could easily exclude them, for example with purgechar().

- Marcus

Re: Anyone recognise these characters from Excel?

atoz1158 — Tue, 12 Jun 2018 10:57:21 GMT

Another way of doing is to use the following that will only keep the upper and lower case letters plus numbers and spaces add others if you need them.

KeepChar(Field1,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879 ') AS Field1

Regards

Adrian