Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create an index into a load script

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

1 Solution

Accepted Solutions
adamdavi3s
Master
Master

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

View solution in original post

10 Replies
adamdavi3s
Master
Master

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
Champion III
Champion III

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

Look here:

Autonumber function

adamdavi3s
Master
Master

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
Champion III
Champion III

Got it. Thanks for that.

adamdavi3s
Master
Master

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

Not applicable
Author

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
Champion III
Champion III

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

adamdavi3s
Master
Master

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
Master
Master

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