Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
vlad_komarov
Partner
Partner

Converting str to hex

Hello!

I am debugging a data issue and I need to find the way to convert strings into array of HEX values.

I do have a data set where some of the records contain few extra hidden characters (that's my guess).

all.jpg

In the example above NUM values look the same, but actual length of the NUM string is different (7 characters for the first two records and 10 for the last two).

I've tried to use TRIM() function, but it did not help, so these extra characters are not "spaces".

Converting these strings to HASH values did not help either, all values are identical:

hash.jpg

Looks like these strings contain some characters that are ignored by normal string functions, but creating a problem for my data model since binary values of the NUM field are not identical for these records.

So I would need some kind of String to HEX conversion to detect which characters exist in these records.

I will appreciate any advice.

Thank you!

Vladimir

1 Solution

Accepted Solutions
jonasheisterkam
Partner
Partner

If you want only debug you can use code like this.

Data:

load

Chr(142)&1234 as String

autogenerate (1);

load

Chr(142)&123 as String

autogenerate (1);

C:

load

max(len(String)) as lenght

Resident Data;

let convert = '';

for i=1 to peek('lenght',0,'C')

let convert =  convert&'&num(ord(mid(String,$(i),$(i)+1)),'&chr(39)&'(hex)00'&chr(39)&')';

next;

LOAD

left(''$(convert),len(String)*2) as hex

Resident Data;

View solution in original post

7 Replies
sunny_talwar

Try doing this:

KeepChar(NUM, '0123456789') as NUM1 If NUM only contains numerical values.

HTH

Best,

Sunny

MK_QSL
MVP
MVP

Need your sample data file in excel format !

vlad_komarov
Partner
Partner
Author

Sunny,

It works well for "normal" characters, but not in my case.

Still returns the same string (NUM_HEX and NUM_HEX_LEN are the new columns added😞

hash2.jpg

Regards,
Vladimir

bill_markham

The Ord() function could help you, but you will have to do it 1 character at a time.  Below pasted in from the Help :

ord( s )

ASCII number of first character of string s. The result is an integer.

Example:

ord('A') returns the number 65.

marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe it's a special char like chr(8203) which didn't react on most string-functions like trim() but a replace() or a keepchar() how suggested from sunindia should work. Maybe you could make a loop on ord(mid(num, iterno(), 1) per while len(num) < iterno() to identify each char.

- Marcus

jonasheisterkam
Partner
Partner

If you want only debug you can use code like this.

Data:

load

Chr(142)&1234 as String

autogenerate (1);

load

Chr(142)&123 as String

autogenerate (1);

C:

load

max(len(String)) as lenght

Resident Data;

let convert = '';

for i=1 to peek('lenght',0,'C')

let convert =  convert&'&num(ord(mid(String,$(i),$(i)+1)),'&chr(39)&'(hex)00'&chr(39)&')';

next;

LOAD

left(''$(convert),len(String)*2) as hex

Resident Data;

View solution in original post

vlad_komarov
Partner
Partner
Author

Jonas,

Your code has worked perfectly. Thank you!

The interesting part is that it helped me realize that I've stepped into a known QV issue (Leading 0 in Text field Qlikview transforms data incorrectly). So the problem was not with the special "hidden" characters, but simply with the way QV handles leading zeroes (the truncated NUM value for the case above was = 0000076115).

Thank you for your help!

Regards,

Vladimir