topic Re: need help in QlikView

need help

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I need a clarification on this.

temp1:

LOAD

key,

nam,

doj,

AutoNumber(name&'|'&doj) as samplenum,

age,

sales

FROM

<path>;

Yes,It showing unique numbers for each unique combination of field values

But what is the use of this and where to use particularly?

Any suggestions??

Re: need help

kkkumar82 — Sun, 29 May 2016 14:51:27 GMT

Hi Satish,

The combination you have used is technically known as a composite key, even there is no Auto number function on top it it does work as name&'|'&doj as samplenum.

It is generally used to avoid synthetic keys , for eg another table also contains the same combination if you don't use a composite key with or with out auto Number it will result a synthetic key.

Re: need help

Anonymous — Sun, 29 May 2016 14:52:35 GMT

Assuming you name and doj fields are long text fields then this will turn them into a small numeric field which consume less memory. Plus if it is used to join 2 tables together this small numeric key field will be far more efficient.

Re: need help

swuehl — Sun, 29 May 2016 14:54:15 GMT

A benefit from using the Autonumber() is that it does not need to store the composite key values in the symbol table, hence it saves you some memory.

Re: need help

kkkumar82 — Sun, 29 May 2016 14:57:58 GMT

One more point is , auto number is used to speed up the association because autonumber results a unique number

Re: need help

swuehl — Sun, 29 May 2016 15:28:29 GMT

I don't think that the autonumber will speed up associations or be more efficient in the table records (where the bit stuffed pointers should be the same, regardless if you are using Autonumber() or not).

The only benefit should result from less memory occupied in the symbol tables (no space required for an autonumber value).

Re: need help

Peter_Cammaert — Sun, 29 May 2016 16:06:11 GMT

Well, the symbol table with all composite key strings (or, as Rob thinks, hashes) will be there, but it will only last as long as the script runs.

Re: need help

swuehl — Sun, 29 May 2016 16:10:44 GMT

True, there need to be bookkeeping of autonumber values during script run, as far as I read the previous comments, we were discussing performance increase w.r.t. joins and linking of tables.

I don't think that using Autonumber() will do anything different / better than just using the composite keys.

Re: need help

Peter_Cammaert — Sun, 29 May 2016 16:23:00 GMT

"...and where to use particularly?"

Never use autonumber() to generate values that travel outside of the current data model (e.g. for storing in a QVD). Because there is no symbol table that survives the current load script run, an autonumber() call on the same key fields in another load script may generate a similar set of values that are linked to entirely different composite key strings.

Peter

Re: need help

swuehl — Sun, 29 May 2016 16:34:41 GMT

Using the second argument to the Autonumber() function, you can also create indices from distinct autonumber "buckets", e.g.

LOAD

Customer,

OrderID,

OrderDate,

Autonumber(OrderID, Customer) as OrderIndexCustomer,

...

RESIDENT INPUT

ORDER BY OrderDate asc;

will create an index on orders for each customer separately (order by was used to order the index same way as the order dates). You can create the index also using a table sorted by Date and Customer, and using Rangesum() and Peek(), this is just another use case I make use of the Autonumber() from time to time.