

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Base HEX to ASCII Challenge
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
D0
D1 J
D2 K
D3 L
D4 M
D5 N
D6 O
D7 P
D8 Q
D9 R
E1
E2 S
E3 T
E4 U
E5 V
E6 W
E7 X
E8 Y
E9 Z
60 -
40
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');


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
D0
D1 J
D2 K
D3 L
D4 M
D5 N
D6 O
D7 P
D8 Q
D9 R
E1
E2 S
E3 T
E4 U
E5 V
E6 W
E7 X
E8 Y
E9 Z
60 -
40
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');


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Jonathan, it works like a charm. I forgot how useful could be the function mapsubstring.
Regards, Marcel.
