Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

atkinsow
Valued Contributor II

Convert String to Hexadecimal Key

Hi, I'm fairly new to qlik but from what I understand you can generate an integer key on a string by using autogenerate().  The problem as I understand is that using autogenerate() doesn't keep your key consistent necessarily.  For example using autogenerate(Products)  on Table A may not give you the same result as autogenerate(Products) on Table B because of the nature of data on each table.

Assuming I'm correct. (Big assumption) 

Can I have Qlikview generate a key that will always generate the same exact key based on the string value?  For instance, if my Product is Apples how can I get Qlikview to always generate the same key value regardless of what qvd or table it is in?  I was thinking you could convert the string of "Apples" to hexadecimal or binary and then use that as the key?  Is that viable?  Has anyone done that?  Did you use a code in a module or is there a native qlikview command?

My real life example is this, I have a QVD X that has ToUSDExchangeRate, Trans Currency, Year, Month, Date.  And then I have QVD Y that has RecordID, Trans Currency, Charge, Year, Month, Date. 

I was thinking I would add hex keys to both QVDs at time of QVD creation that represent the Date and Trans Currency concatenated.  It would enable me to link the 2 QVDs together easily whenever I need to.  I would prefer not linking on concatenated string fields..

Thanks for your thoughts and ideas!

1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

Re: Convert String to Hexadecimal Key

This is what I ended up with guys.  It seems to work. 

I added some vb code in the module.

**************************************************************************************

Function StringToHex(strInput)

    For I = 1 To Len(strInput)

        strTemp = Hex(Asc(Mid(strInput, I, 1)))

        If Len(strTemp) = 1 Then strTemp = "0" & strTemp

        strReturn = strReturn &  strTemp

    Next

    StringToHex = strReturn

End Function

**************************************************************************************

And then I referenced it in the script like this:

Load

StringToHex(MyField1)

I don't know if doing that is better or worse than just using a concatenated string field as a key, but it's another tool for the toolbox!

14 Replies

Re: Convert String to Hexadecimal Key

If you are looking for this in a particular field, then AutoNumber() will do exactly what you are looking for

atkinsow
Valued Contributor II

Re: Convert String to Hexadecimal Key

Sorry I meant autonumber() not autogenerate().

MVP
MVP

Re: Convert String to Hexadecimal Key

I don't know if I'm disagreeing or clarifying since you linked to the hash functions, but...

Autonumber() is specific to a single script. So assuming the two QVDs are being built by two different scripts, autonumber() will not produce the result we want. What WILL produce the result we want is autonumberhash128() or autonumberhash256(). Both of those create hash keys instead of consecutive integers. However, since those are creating 128 and 256-bit keys, I'm betting that concatenating the date and currency together will create a smaller key, and we shouldn't use either hash function for this specific case.

Edit: If the two QVDs are being built by the SAME script, then autonumber() will work.

Edit: Wait, what? AutonumberhashXXX() is ALSO specific to a single script? We have to use hashXXX()? Huh. Sorry. My mistake I guess.

Re: Convert String to Hexadecimal Key

I am glad that you are clarifying John. To tell you the truth, I have never used AutoNumberHash before and don't really understand the difference between the three options. I guess I need to get my basics right before helping others on this topic. But on the other hand, if I would not have commented, I would have never got the encouragement to learn something new. I apologize for mis-guiding the OP.

Re: Convert String to Hexadecimal Key

and I was under the impression that both qvds were built in the same qvd generator.

Re: Convert String to Hexadecimal Key

So many edits John, stop confusing me

MVP
MVP

Re: Convert String to Hexadecimal Key

Except that it looks like I'm wrong as well. AutonumberhashXXX() is apparently specific to one script. So... what's the point of it? A straight autonumber() will give you a smaller key and work just fine for a single script. If we have to use hashXXX() to be dependent ONLY on the value we give it, in what situation would it ever be advantageous to use autonumberhashXXX()?

Edit: More edits! I guess I should have researched before replying, but I thought I knew this one. The autonumberhashXXX() calculates a hash key... then assigns an integer to it. I still don't see the point of it, but now I don't see the point of it for a different reason - how is that any different from autonumber() except for being slower? Honestly, I've never used any of them. I fear making a mistake with autonumber() and accidentally using it across scripts somehow without realizing it, like if I convert a regular load into an incremental load, say, and autonumber() my incremental file before reading in the main file. And I've never had a case where I thought it was worth calculating a hash key.

Re: Convert String to Hexadecimal Key

Hi John,

what I like about the AutonumberHash() functions is that they are taking as many different fields/expressions as parameters as you like, so I don't have to worry about potential collisions when concatenating strings with similar values or delimiters that might occur in the data as well.

At the same time I like Autonumber() for its ability to generate independent number series using its second parameter.

regards

Marco

Community Browser