Convert String to Hexadecimal Key

[Anonymous]

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!

[Anonymous]

Sorry for not being clearer.  They are in 2 different scripts so autonumber won't necessarily give me the same result.

[Anonymous]

Hey guys,

Thanks for all your help!

I found this while looking around.  Would something like this work to always generate the same key based on the string value regardless of whether it's in the same script or not?

num#(mystringfield, '(HEX)' )

[MarcoWedel]

only if mystringfield contains hexadecimal (text-) values.

regards

Marco

[Anonymous · Accepted solution]

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!

[Peter_Cammaert]

Found out rather late about this discussion. We had it before (Rob and the gang) here.Autonumber or Autonumber#

I guess it all has to do with Symbol tables. I can't get a final grip on it yet (I agree that I stopped trying a while ago because of lack of time) but my intuition tells me that there is a big advantage to AutoNumberHashXXX() functions: they never store the actual string value in a symbol table. Only useful for extremely large strings, which IMHO cause trouble in symbol tables. But that's the main part I still haven't got a good grip on.

Peter