I need help with row numbering

[Xabinav]

CarID	OldCycle	IsRealCycle?	NewCycle (Trying to solve)
1234567	1	1	1
1234567	2	0	1
1234567	3	0	1
1234567	4	1	2
1234567	5	0	2
1234567	6	1	3
1234567	7	1	4

Hello everyone,

I'm trying to develop the field "NewCycle".

The logic is that every time that appears "1"  in the field "IsRealCycle?" , you need to "Jump" at 1 relative to its previous digit.

Another example...

CarID	OldCycle	IsRealCycle?	NewCycle (Trying to solve)
2222333	1	1	1
2222333	2	0	1
2222333	3	0	1
2222333	4	0	1
2222333	5	1	2

Thanks,

[sergio0592]

Hi,

It works as expression with:

rangesum(above (TOTAL IsRealCycle?,0,RowNo(TOTAL)))

[Xabinav]

Thanks !!!

There is a way to write it in script  ?

[sergio0592]

Hum, this doesn't works in script. I thing Above function is only for front end scripting. But in the online help it should works in load statement and chart script. If any experts can advise.

[sunny_talwar · Accepted solution]

Try this to do this in the script

Table:

LOAD * INLINE [

    CarID, OldCycle, IsRealCycle?

    1234567, 1, 1

    1234567, 2, 0

    1234567, 3, 0

    1234567, 4, 1

    1234567, 5, 0

    1234567, 6, 1

    1234567, 7, 1

    2222333, 1, 1

    2222333, 2, 0

    2222333, 3, 0

    2222333, 4, 0

    2222333, 5, 1

];

FinalTable:

LOAD *,

If(CarID = Previous(CarID), RangeSum(Peek('NewCycle'), IsRealCycle?), IsRealCycle?) as NewCycle

Resident Table

Order By CarID, OldCycle;

DROP Table Table;

[sergio0592]

Please Sunny can you explain RangeSum(Peek('NewCycle'), IsRealCycle?)?

[sunny_talwar]

Peek and Previous are script equivalent (loosely speaking) of Above() function on the front end.... You can read more about them here

Peek() vs Previous() – When to Use Each

[ujjwalraja]

Hi you can use this approach

tempTable:

LOAD * INLINE [

    CarID, OldCycle, IsRealCycle?

    1234567, 1, 1

    1234567, 2, 0

    1234567, 3, 0

    1234567, 4, 1

    1234567, 5, 0

    1234567, 6, 1

    1234567, 7, 1

    2222333, 1, 1

    2222333, 2, 0

    2222333, 3, 0

    2222333, 4, 0

    2222333, 5, 1

];

table:

load * , if(rowno()=1,IsRealCycle?,IsRealCycle?+peek('NewCycle'))as NewCycle

Resident Table;

drop Table tempTable;

[sunny_talwar]

This will continue to accumulate regardless of change in CarID.... Not sure if that is what the OP is looking to get...

[ujjwalraja]

Yes, depends upon his requirement.