topic Re: How to create an index into a load script in QlikView

How to create an index into a load script

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a table with records on purchase order lines; these data contains the purchase order lines transactions history.

I have no unique key and wish to create an index which I will use to join data from other tables.

Many thanks in advance for any help.

Best regards

Re: How to create an index into a load script

adamdavi3s — Fri, 10 Mar 2017 14:48:57 GMT

Hi,

You can use the hash128() function to create a nice quick key for Qlikview to use.

So just do hash128(field1,field2,field3) using the fields which make your rows unique.

Re: How to create an index into a load script

vishsaggi — Fri, 10 Mar 2017 14:52:29 GMT

You can use Autonumber() function on your fields to create a key field.

Look here:

Autonumber function

Re: How to create an index into a load script

adamdavi3s — Fri, 10 Mar 2017 14:55:06 GMT

Autonumber will NOT create the same number on another table given the same input.

Hash is required to ensure the link between tables will work.

Thank you rwunderlich‌ for perfectly wording this:

Hash functions are deterministic, that is, given the same input, they will always produced the same output. AutoNumber() is non-deterministic.Output values are dependent on the load order of input. For that reason, Autonumbering of keys should only be done in the final qvw. Autonumbered keys should not be stored in QVDs or intermediate qvws.

*edit*

Yes I am slightly mistaken I think (thank you for this as I have used it to clarify my understanding), if using the autonumber() function more than once within the same script then you're OK, but ONLY if it is all in the same run.

I've always used hash128() out of habit as 90% of what I do is using QVDs

All 3 of the autonumber functions have one pitfall, the lookup table(s) exist only whilst the current script execution is active. After the script completes, the lookup table is destroyed and so the same expression value may be assigned different integer values in different script executions. This means that the autonumber functions can't be used for key fields within incremental loads

QlikView Addict: QlikView Functions: autonumber()

Re: How to create an index into a load script

vishsaggi — Fri, 10 Mar 2017 15:06:49 GMT

Got it. Thanks for that.

Re: How to create an index into a load script

adamdavi3s — Fri, 10 Mar 2017 15:11:07 GMT

I may be mistaken, but that's what I have always understood!

Re: How to create an index into a load script

Fri, 10 Mar 2017 15:15:08 GMT

Hello Adam and Vishwarath,

Thank you to both I will test both methods but think that to aggregate data which contain PO number, PO line number and PO SubLine number (data I can concatenate), I will use Hash function.

Best regards.

Re: How to create an index into a load script

vishsaggi — Fri, 10 Mar 2017 15:15:49 GMT

I completely forgot about reading what Rob mentioned. However, just wondering cant we use AutonumberHash128() to get the unique integer for distinct value?

Re: How to create an index into a load script

adamdavi3s — Fri, 10 Mar 2017 15:15:50 GMT

I've always use

QlikView Addict: QlikView Functions: autonumber()

Re: How to create an index into a load script

adamdavi3s — Fri, 10 Mar 2017 15:16:51 GMT

see what I have just posted

Personally autonumberhash is pointless as it has the same limitations as autonumber, either use autonumber (no qvd's, no partial reloads etc) or hash128()

Re: How to create an index into a load script

vishsaggi — Fri, 10 Mar 2017 15:18:49 GMT

Just read it. Got it now. Thanks for sharing this info. And thanks for asking Idriss.