Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reading HEX values from a database

Hi,

I am trying to read the following from a progress database.

Field

Value

A

332400

B

Test product

C

322C303231332C31392F30382F31302C30333A32303A33342C2C2C2C302C2C2C302C30

D

12/12-2010

The information reads in exactly as above, filed C is a RAW data (HEX) representation of

2,0213,19/08/10,03:20:34,,,,0,,,0,0

I would like to be able to read each one of the comma separated values into its own QlikView field. I am at a total lost so any help would be appreciated.

Gavin.

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

you could use the script below to convert the Hex-String into a Ascii String.

org:
LOAD * INLINE [
A, B, C, D
332400, Test product, 322C303231332C31392F30382F31302C30333A32303A33342C2C2C2C302C2C2C302C30, 12/12-2010
];


tmpHexTable:
LOAD
recno()+31 AS Nr
, CHR(recno()+31) AS Ascii
, NUM(recno()+31,'(HEX)00') AS Hexa
AUTOGENERATE 95;

HexTable:
MAPPING
LOAD
Hexa, Ascii
RESIDENT tmpHexTable;
DROP TABLE tmpHexTable;


new:
LOAD
*
, MAPSUBSTRING('HexTable', C) AS C2
RESIDENT org;

DROP TABLE org;

Then, you could use the subfield function to get each of the Contents of the Comma seperated fields of C2.

Hope that helps.

Best regards

Michael

View solution in original post

3 Replies
Not applicable
Author

Hi,

you could use the script below to convert the Hex-String into a Ascii String.

org:
LOAD * INLINE [
A, B, C, D
332400, Test product, 322C303231332C31392F30382F31302C30333A32303A33342C2C2C2C302C2C2C302C30, 12/12-2010
];


tmpHexTable:
LOAD
recno()+31 AS Nr
, CHR(recno()+31) AS Ascii
, NUM(recno()+31,'(HEX)00') AS Hexa
AUTOGENERATE 95;

HexTable:
MAPPING
LOAD
Hexa, Ascii
RESIDENT tmpHexTable;
DROP TABLE tmpHexTable;


new:
LOAD
*
, MAPSUBSTRING('HexTable', C) AS C2
RESIDENT org;

DROP TABLE org;

Then, you could use the subfield function to get each of the Contents of the Comma seperated fields of C2.

Hope that helps.

Best regards

Michael

Not applicable
Author

Michael,

Thank you so much for your help, worked exactly how I was hoping. I can now read in all the required information and not have to worry about batch files dumping the data out over night. I added al little bit of code to strip out the UNICODE-8 characters that are on the end of a line

BuildHexTable:

LOAD

recno() AS Nr

,CHR(recno()-1) AS Ascii

,NUM(recno()+9,'(HEX)00') AS Hexa

AUTOGENERATE 1;

BuildHexTable:

LOAD

recno()+1 AS Nr

,CHR(recno()-1) AS Ascii

,NUM(recno()+12,'(HEX)00') AS Hexa

AUTOGENERATE 1;

Everything else worked brilliantly, would have struggled for a long time before thinking of using a mapping solution.

Thank you again for your time & help.

Gavin.

Not applicable
Author

Very good solution ... thanks!