topic Re: Base HEX to ASCII Challenge in QlikView

Base HEX to ASCII Challenge

marcel_olmo — Thu, 06 Aug 2015 10:05:16 GMT

Hi guys,

I have a field in Hex code and I need to convert it into ASCII Code.

Here's an example of what I'm saying with the Hexcode and my desired result :

ID	HEX	DesiredResult
1	C2D6C2C9D54BD9C5E3D9C1C3E3C9D340D4C1D54BF5F0C3D440F2F3D4C9C3	BOBIN.RETRACTIL MAN.50CM 23MIC
2	C260E2C1D540C2C1D9D84BC3C8D6C3D640E2C9D540C1E94B404040404040	B-SAN BARQ.CHOCO SIN AZ.
3	C240D5C9E3C1C3D9C5D44040404040404040404040404040404040404040	B NITACREM

I have a table of 70 rows with the correlation between HEX and ASCII :

BaseHex	BaseABC
C1	A
C2	B
C3	C
C4	D
C5	E
…	…

I'm trying to do it as optimal as possible.

A possible solution would be using replace() function 70 times in order to change the HEX origin into DesiredResult. But I'm trying to do it using some kind of loop thing with mapping tables.

How will you solve that?

I've attached the two origins in excel if you want to try.

Regards, Marcel.

Re: Base HEX to ASCII Challenge

jonathandienst — Thu, 06 Aug 2015 10:24:03 GMT

This works:

MapHexToAscii:

Mapping LOAD *

Inline

[

Code Char

C1 A

C2 B

C3 C

C4 D

C5 E

C6 F

C7 G

C8 H

C9 I

D1 J

D2 K

D3 L

D4 M

D5 N

D6 O

D7 P

D8 Q

D9 R

E2 S

E3 T

E4 U

E5 V

E6 W

E7 X

E8 Y

E9 Z

60 -

4B .

F0 0

F1 1

F2 2

F3 3

F4 4

F5 5

F6 6

F7 7

F8 8

F9 9

6C %

61 /

7B Ñ

4E +

7F "

4D (

5D )

6B ,

7D '

] (delimiter is '\t');

Data:

LOAD ID, HEX, MapSubstring('MapHexToAscii', HEX) As Decode

Inline

[

ID HEX

1 C2D6C2C9D54BD9C5E3D9C1C3E3C9D340D4C1D54BF5F0C3D440F2F3D4C9C3

2 C260E2C1D540C2C1D9D84BC3C8D6C3D640E2C9D540C1E94B404040404040

3 C240D5C9E3C1C3D9C5D44040404040404040404040404040404040404040

] (delimiter is '\t');

Re: Base HEX to ASCII Challenge

jonathandienst — Thu, 06 Aug 2015 10:36:30 GMT

Re: Base HEX to ASCII Challenge

jonathandienst — Thu, 06 Aug 2015 10:41:03 GMT

I see the inline load is failing to pick up the space for code 40. This would probably not be a problem when reading from a file, but here is a quick workaround:

LOAD ID, HEX, MapSubstring('MapHexToAscii', Replace(HEX, '40', ' ')) As Decode

Re: Base HEX to ASCII Challenge

petter — Thu, 06 Aug 2015 10:49:07 GMT

This must be a IBM EBCDIC to ANSI character set conversion thing.

QlikView is able to read files in EBCDIC code pages. So wouldn't it be easier to specify this in your LOAD-statement?

If it is coming from a database the ODBC-driver should be configurable to handle EBCDIC to ASCII/ANSI conversion.

Re: Base HEX to ASCII Challenge

marcel_olmo — Thu, 06 Aug 2015 10:59:28 GMT

Thanks Jonathan, it works like a charm. I forgot how useful could be the function mapsubstring.

Regards, Marcel.