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!

[sunny_talwar]

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

[Anonymous]

Sorry I meant autonumber() not autogenerate().

[sunny_talwar]

Look for them in QV Help:

1) https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/auton...

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

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

[johnw]

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.

[sunny_talwar]

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.

[sunny_talwar]

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

[sunny_talwar]

So many edits John, stop confusing me

[johnw]

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.

[MarcoWedel]

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