topic Re: Convert String to Hexadecimal Key in QlikView

Convert String to Hexadecimal Key

Anonymous — Fri, 08 Apr 2016 20:44:02 GMT

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!

Re: Convert String to Hexadecimal Key

sunny_talwar — Fri, 08 Apr 2016 21:35:11 GMT

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

Re: Convert String to Hexadecimal Key

Anonymous — Fri, 08 Apr 2016 21:47:33 GMT

Sorry I meant autonumber() not autogenerate().

Re: Convert String to Hexadecimal Key

sunny_talwar — Fri, 08 Apr 2016 22:14:32 GMT

Look for them in QV Help:

1) https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/autonumber.htm?q=Autonumb…

2) https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/autonumberhash128.htm‌

3) https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/autonumberhash256.htm

Re: Convert String to Hexadecimal Key

johnw — Fri, 08 Apr 2016 22:32:25 GMT

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

sunny_talwar — Fri, 08 Apr 2016 22:35:48 GMT

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

sunny_talwar — Fri, 08 Apr 2016 22:37:05 GMT

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

Re: Convert String to Hexadecimal Key

sunny_talwar — Fri, 08 Apr 2016 22:37:52 GMT

So many edits John, stop confusing me

Re: Convert String to Hexadecimal Key

johnw — Fri, 08 Apr 2016 22:38:25 GMT

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

MarcoWedel — Sat, 09 Apr 2016 00:25:59 GMT

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

Re: Convert String to Hexadecimal Key

Anonymous — Mon, 11 Apr 2016 14:19:24 GMT

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

Re: Convert String to Hexadecimal Key

Anonymous — Mon, 11 Apr 2016 14:30:07 GMT

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

Re: Convert String to Hexadecimal Key

MarcoWedel — Mon, 11 Apr 2016 19:44:30 GMT

only if mystringfield contains hexadecimal (text-) values.

regards

Marco

Re: Convert String to Hexadecimal Key

Anonymous — Fri, 15 Apr 2016 15:58:10 GMT

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

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!

Re: Convert String to Hexadecimal Key

Peter_Cammaert — Fri, 15 Apr 2016 17:40:23 GMT

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