How to convert/map a string key to a unique number key?

[Anonymous]

Dear All,

Is there anyway to convert/map a string key to a unique number key?

This unique number should not change in any condition, such as belong to number of records when loading.

Thank you.

[luismadriz]

Hi,

Is AutoNumber useful for you?

[luismadriz]

Also this one may be helpful: AutoNumber() vs Auto..Hash()

Cheers,

Luis

[Anonymous]

Thank you.

However, in case of reloading data with difference number of records of table, that number key will be different.

Example:

- firstly, I load table with fromdate (1/1/2017) and todate (30/01/2017), I use Autonumber() or AutonumberHash() convert string key to unique key (unique key 1).

-second, I load the same table (with same data), but fromdate (15/01/2017) and todate (30/01/2017). I also use Autonumber() or AutonumberHash() convert string key to unique key (unique key 2).

=> the problem is unique key 1 different unique key 2

[Anil_Babu_Samineni]

Yes, from here even it works

AutoNumber(fromdate & '|' & todate) as [unique key]

[luismadriz]

Yes, you're right, that's why I wasn't so sure if AutoNumber was going to help you

I remember I solved something similar in Oracle DB with something like ORA_HASH..... that was ages ago, I can't remember much about if now. There is a Hash* function in Qlik but it returns a unique (and deterministic) string of characters but you're asking for numbers...

I'm interested in finding this out as well, I'll keep looking and thinking...

If you don't mind my asking, why is it that you have such requirement?

Good luck,

Luis

PS, you may have to do it outside of Qlik and then being in the table for mapping or associating

[Anonymous]

No, I mean that :

- firstly, I load table A with fromdate (1/1/2017) and todate (30/01/2017), about 1000 records, I use Autonumber() or AutonumberHash() convert string key to unique key (unique key 1).

A: AutonumberHash(date, dsr, cutomer) as uniquenumber_1, sale, stock, ....

-second, I load the same table A (with same data), but fromdate (15/01/2017) and todate (30/01/2017), about 500 records. I also use Autonumber() or AutonumberHash() convert string key to unique key (unique key 2).

A: AutonumberHash(date, dsr, cutomer) as uniquenumber_2, sale, stock, ....

uniquenumber_1 different uniquenumber_2

[Anonymous]

Thank you for your discussion!

I have that problem in case my data have error or missing someday, so I have to reload just this day. Then the numberkey (generated from AutonumberHash128() function) is not matching.

PS, you may have to do it outside of Qlik and then being in the table for mapping or associating => because the data is large and the number of operation much, so we can't do manual like this.

[luismadriz]

Then Hash256() should work

[marcus_sommer]

If it's the last step within an application you could use one of the autonumber-functions for the key. But if you need the key during the ETL process this won't be valid in the most cases (exceptions are thinkable if you could definitely ensure the load-order and that only new values could be added but it's rather a unlikely scenario and needs some efforts to chekc the logic).

But this meant not mandatory that a converting from a string to a number isn't possible but it depends on the needed keys - how does they look like?

- Marcus