Announcements
cancel
Showing results for
Did you mean:
Creator

## I need help with row numbering

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?
2222333 1 1 1
2222333 2 0 1
2222333 3 0 1
2222333 4 0 1
2222333 5 1 2

Thanks,

Labels (1)
• ### Application Development

1 Solution

Accepted Solutions
MVP

Try this to do this in the script

Table:

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:

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

Resident Table

Order By CarID, OldCycle;

DROP Table Table;

10 Replies
Specialist III

Hi,

It works as expression with:

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

Creator
Author

Thanks !!!

There is a way to write it in script  ?

Specialist III

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.

MVP

Try this to do this in the script

Table:

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:

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

Resident Table

Order By CarID, OldCycle;

DROP Table Table;

Specialist III

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

MVP

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

Contributor III

Hi you can use this approach

tempTable:

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:

Resident Table;

drop Table tempTable;

MVP

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

Contributor III

Yes, depends upon his requirement.

Community Browser