Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Replace %Hex with character

Hi

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Replace %Hex with character

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
MVP
MVP

Re: Replace %Hex with character

Hi

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

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rupe
Valued Contributor

Re: Replace %Hex with character

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

rupe
Valued Contributor

Re: Replace %Hex with character

Jonathan's approach is much simpler I think

MVP
MVP

Re: Replace %Hex with character

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
preminqlik
Valued Contributor II

Re: Replace %Hex with character

how should your output will be ? ..

rupe
Valued Contributor

Re: Replace %Hex with character

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

Re: Re: Replace %Hex with character

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

MVP
MVP

Re: Replace %Hex with character

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Replace %Hex with character

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

Community Browser