How to create an index into a load script

[#1]

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

[adamdavi3s]

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.

[vishsaggi]

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

Look here:

Autonumber function

[adamdavi3s · Accepted solution]

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

[vishsaggi]

Got it. Thanks for that.

[adamdavi3s]

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

[#7]

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.

[vishsaggi]

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

[adamdavi3s]

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 use

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

[adamdavi3s]

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