topic Re: Replace %Hex with character in QlikView

Replace %Hex with character

Anonymous — Tue, 29 Apr 2014 10:42:53 GMT

In my source data I have a field that contains data which has %Hex characters in it, as per this example:

PCI20 - %C2%BFEl agente env%C3%ADa la bolsa por la cinta antes de que se sirve la pr%C3%B3xima pax%3F

What I would like to is search the string for all the % 's and for each one translate the next 2 characters to its printable character, as in replace the %3F at the end with ?

Any ideas as I am flummoxed as how to approach this ?

Best Regards, Bill Markham

Re: Replace %Hex with character

jonathandienst — Tue, 29 Apr 2014 10:49:17 GMT

Create a mapping table with all the substitutions and then use MapSubString().

HTH

Jonathan

Re: Replace %Hex with character

Roop — Tue, 29 Apr 2014 10:52:10 GMT

There is no Hex conversion that I know of but it could easily be created manually (as it never changes).

I would create a Hex conversion table and use the table in an ApplyMap for the lookup of the character sets.

In order to parse the field into its constituent parts, I would use Substringcount to get the number of "%"s and perform a loop through the "%" using subfield to extract the Nth part of the array.

I hope this helps and sorry I do not have time to work it fully through

Re: Replace %Hex with character

Roop — Tue, 29 Apr 2014 10:54:24 GMT

Jonathan's approach is much simpler I think

Re: Replace %Hex with character

jonathandienst — Tue, 29 Apr 2014 10:59:45 GMT

Bill

I don't think you can get a space as a value in an inline load, so I would do something like this:

MapCodes:

Mapping LOAD

Code,

Replace(Value, '\*', ' ') As Value

Inline

[

Code, Value

%20%, \*

%27%, '

...

];

Jonathan

Re: Replace %Hex with character

preminqlik — Tue, 29 Apr 2014 11:05:41 GMT

how should your output will be ? ..

Re: Replace %Hex with character

Roop — Tue, 29 Apr 2014 11:08:12 GMT

or just create it as a file so that it does not require an inline load ......

Re: Re: Replace %Hex with character

Anonymous — Tue, 29 Apr 2014 11:22:44 GMT

Jonathan

Your suggestion looks promising, but I do not know in advance what the hex codes will be. The string comes from a language translation table, so the hex could any of the hex codes for characters not on a standard UK English keyboard. Initially just Spanish language, but later many more languages.

I reckon the MapSubString() you suggested is going to be useful.

But also i have found this using my friend Google:

=chr(Num#('3F', '(HEX)')) which from hex 3F returns it printable character ?

Now I just need to somehow mash this up into something workable. [Maybe some cunning dollar expansion?]

Best Regards, Bill

Re: Replace %Hex with character

jonathandienst — Tue, 29 Apr 2014 14:08:54 GMT

Bill

If your encoding is UTF-8, you should be able to automatically load all the mappings using:

MapAsciiCodes:

Mapping LOAD

'%' & Num(RowNo() + 31, '(HEX)') As Code,

Chr(RowNo() + 31) As Value

AutoGenerate 256 - 32;

So that will have all the printable values up to %FF (0 - 31 are not printable)

HTH

Jonathan

Re: Replace %Hex with character

Anonymous — Tue, 29 Apr 2014 17:15:33 GMT

Jonathan

Many thanks for that cunning bit of script to autogenerate the Ascii Mapping Table.

It is all looking very promising, so thanks again.

Best Regards, Bill