topic Converting str to hex in QlikView

Converting str to hex

vlad_komarov — Tue, 07 Jul 2015 17:54:29 GMT

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

Re: Converting str to hex

sunny_talwar — Tue, 07 Jul 2015 17:57:37 GMT

Try doing this:

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

HTH

Best,

Sunny

Re: Converting str to hex

MK_QSL — Tue, 07 Jul 2015 17:58:27 GMT

Need your sample data file in excel format !

Re: Converting str to hex

vlad_komarov — Tue, 07 Jul 2015 18:18:27 GMT

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

Re: Converting str to hex

Anonymous — Tue, 07 Jul 2015 18:22:03 GMT

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.

Re: Converting str to hex

marcus_sommer — Tue, 07 Jul 2015 19:10:29 GMT

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

Re: Converting str to hex

jonasheisterkam — Tue, 07 Jul 2015 19:39:06 GMT

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

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;

Re: Converting str to hex

vlad_komarov — Tue, 07 Jul 2015 22:42:15 GMT

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