Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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');
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');
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
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.
Thanks Jonathan, it works like a charm. I forgot how useful could be the function mapsubstring.
Regards, Marcel.