topic Creating a numeric key in QlikView

Creating a numeric key

Thu, 07 Apr 2011 12:03:05 GMT

Hey Guys.

I have the following table:

Now I want that if the CostingCode=0 then the numerator will not appear and the next one will continue from where is stopped.

Now the table will look like this:

How can I do it in the script? (I have to do it for every invoice...)

Thanks in advance!!

Montal.

Thu, 07 Apr 2011 12:09:28 GMT

Hi Montal,

Try this if (CostingCode=0,' -' , Numerator)

Thu, 07 Apr 2011 12:15:20 GMT

Hi XXX.

But then the last Numertaor will be 6 and it needs to be 4...

Thu, 07 Apr 2011 12:38:57 GMT

Hi,

How you are genrating the numerator, it is database value of generated at script level using recno ,row no() functions.

Thu, 07 Apr 2011 12:40:37 GMT

Hi,

Sort the table by Invoice No & Numerator then use below if statment

if(peek(CostingCode)=0,peek(Numerator),Numerator)

Hope this will help you

Thanks & Regards

Thu, 07 Apr 2011 12:56:17 GMT

Hi Upendra ,

I tried your if condition but getting wrong value. Please correct where it is going wrong

[Invoice No],

LOAD

CostingCode,

if(peek(CostingCode)=0,peek(Numerator),Numerator) as Numerator

FROM (ooxml, embedded labels, table is Sheet1) order by [Invoice No],CostingCode asc

Attached the o/p for your reference.

tabletuner — Thu, 07 Apr 2011 13:10:45 GMT

apply the peek() function in a resident load AFTER the initial load. Sort the table in your initial load.

Also, include a statement like if(peek('invoice')=invoice),.....) because you need to restart if a new invoice appears.