topic How to convert/map a string key to a unique number key? in QlikView

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

Anonymous — Wed, 13 Dec 2017 03:45:08 GMT

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.

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

luismadriz — Wed, 13 Dec 2017 05:20:19 GMT

Hi,

Is AutoNumber useful for you?

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

luismadriz — Wed, 13 Dec 2017 05:26:14 GMT

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

Cheers,

Luis

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

Anonymous — Wed, 13 Dec 2017 07:15:40 GMT

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

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

Anil_Babu_Samineni — Wed, 13 Dec 2017 07:35:22 GMT

Yes, from here even it works

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

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

luismadriz — Wed, 13 Dec 2017 07:41:25 GMT

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

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

Anonymous — Wed, 13 Dec 2017 07:48:37 GMT

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

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

Anonymous — Wed, 13 Dec 2017 07:54:19 GMT

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.

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

luismadriz — Wed, 13 Dec 2017 07:56:47 GMT

Then Hash256() should work

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

marcus_sommer — Wed, 13 Dec 2017 08:21:30 GMT

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

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

Anonymous — Wed, 13 Dec 2017 10:12:17 GMT

I need convert to number for performance

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

Anonymous — Wed, 13 Dec 2017 10:16:04 GMT

If there is anyway or function to map from one group of string to one exactly and unique number?

If there is one, I think this problem would be fixed.

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

marcus_sommer — Wed, 13 Dec 2017 10:35:44 GMT

AFAIK there don't exists such a function in Qlik but it's (in general) possible to build such a functionality whereby like above mentioned it depends on how the keys look like. If are the key-parts numbers there is a real chance to get it to work - if the key-parts are strings (which really couldn't be replaced by an ID) it's rather unlikely especially because Qlik treated only values until 14-digits as number (in special cases also 15-digits) but after that it's also a string.

Therefore how does the keys look like?

- Marcus

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

Anil_Babu_Samineni — Thu, 21 Dec 2017 09:06:48 GMT

Then, May be this?

Load * From Table2 ;

Load * From Table1 Where Not Exists(uniquenumber_1, uniquenumber_2);

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

acardella — Mon, 21 Aug 2023 08:51:46 GMT

just remeber...

AutonumberHash only works into same reload app, not across applications, because it depends also from data load order.

As Maria said (@JaMajka1), you can use Hash() but result is in string format, not a scalar

https://community.qlik.com/t5/QlikView-App-Dev/use-of-hash-function/m-p/1385736#M805773