Converting str to hex

[vlad_komarov]

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).

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:

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

[sunny_talwar]

Try doing this:

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

HTH

Best,

Sunny

[MK_QSL]

Need your sample data file in excel format !

[vlad_komarov]

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😞

Regards,
Vladimir

[Anonymous]

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]

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 · Accepted solution]

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;

[vlad_komarov]

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