topic Re: Is there a way to make a load incrementing a variable? in QlikView

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

rcandeo — Fri, 30 Aug 2013 20:53:22 GMT

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?

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

hic — Sat, 31 Aug 2013 08:21:50 GMT

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

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

luciancotea — Sat, 31 Aug 2013 09:29:20 GMT

... a small modification:

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

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

Oleg_Troyansky — Sun, 01 Sep 2013 16:53:37 GMT

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