Creating a numeric key

[#1]

Hey Guys.

I have the following table:

Numerator	CostingCode	Invoice No
1	1	AAAA-111
2	1	AAAA-111
3	0	AAAA-111
4	1	AAAA-111
5	0	AAAA-111
6	1	AAAA-111

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:

Numerator	CostingCode	Invoice No
1	1	AAAA-111
2	1	AAAA-111
-	0	AAAA-111
3	1	AAAA-111
-	0	AAAA-111
4	1	AAAA-111

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

Thanks in advance!!

Montal.

[#2]

Hi Montal,

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

[#3]

Hi XXX.

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

[#4]

Hi,

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

[#5]

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

[#6]

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 · Accepted solution]

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.