Is there a way to make a load incrementing a variable?

[rcandeo]

Hi, I have a table with DOCUMENT status that has no other key than order number, and I need to create this keys during the load ordering by date.

Something like this will create me this second key beggining from 1 for each DOCUMENT:

TABLE1:

LOAD

     DOCUMENT,

     DATE,

     STATUS

FROM TABLE;

vkey = 1;

vlastkey = 0;

DOCUMENTS:

LOAD

     DOCUMENT,

     DATE,

     STATUS,

     IF(vlastkey <> DOCUMENT, 1, vkey) as DETAIL_KEY,

     LET vkey =  IF(vlastkey <> DOCUMENT, 1, vlastkey + 1 as vlastkey,

     LET vlastkey = DOCUMENT,

RESIDENT TABLE1 ORDER BY DOCUMENT, DATE;

How can I implement the last 3 lines of this load?

[hic]

I don't know what your data looks like and I am not sure that I have understood what you want to achieve, but whenever you want to have a counter inside a load, the Peek() function is a good tool. And you can use it so that it references the field that you currently create.

You could try something similar to the following

     If(Peek(Key)=DOCUMENT, Peek(Key), RangeSum(Peek(Key),1) ) as Key

In other words: if Peek(Key) = DOCUMENT then you take the same number of Key as in the above record. But DOCUMENT has changed, you increase Key with 1. You need to use RangeSum() since Peek() on the first record will evaluate to NULL.

HIC

[luciancotea]

... a small modification:

If(Peek('Key')=DOCUMENT, Peek('Key'), RangeSum(Peek('Key'),1) ) as Key

[Oleg_Troyansky]

... one more small modification:

If(Peek('DOCUMENT')=DOCUMENT, Peek('Key'), RangeSum(Peek('Key'),1) ) as Key

you tried to compare the DOCUMENT name with the calculated number of the document...

"How many QlikView experts does it take to calculate a counter?"

cheers,

Oleg Troyansky