
Creator III
2023-09-21
11:36 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rangesum and Above / Previous Value in Script, to create a rolling number
Hi,
I am having a table in the script with following fields: Row, PNR and Flag (which can be 0 or 1).
I would like to create now on script level the field "Rangesum", but I dont know how to do it in the script.
It should always sum the current value in the Flag (0 or 1) AND the previous / above values.
Row |
PNR |
Flag |
Rangesum |
1 |
2222 |
0 |
0 |
2 |
2222 |
1 |
1 (0 in above row "Rangesum" +1 in current row "Flag") |
3 |
2222 |
0 |
1 |
4 |
2222 |
0 |
1 |
5 |
2222 |
1 |
2 |
6 |
2222 |
1 |
3 |
7 |
2222 |
0 |
3 |
8 |
2222 |
1 |
4 |
Thanks for your help!
898 Views
1 Solution
Accepted Solutions

Contributor III
2023-09-21
12:00 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using the Rangesum() function with Peek(). Be aware of the apostrophes (') in peek and the Order of the table row is important!
Load
rangesum(Flag,peek('Rangesum_script')) as Rangesum_script,
*;
Load * Inline [
Row PNR Flag Rangesum
1 2222 0 0
2 2222 1 1
3 2222 0 1
4 2222 0 1
5 2222 1 2
6 2222 1 3
7 2222 0 3
8 2222 1 4
] (delimiter is '\t');
2 Replies

Contributor III
2023-09-21
12:00 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using the Rangesum() function with Peek(). Be aware of the apostrophes (') in peek and the Order of the table row is important!
Load
rangesum(Flag,peek('Rangesum_script')) as Rangesum_script,
*;
Load * Inline [
Row PNR Flag Rangesum
1 2222 0 0
2 2222 1 1
3 2222 0 1
4 2222 0 1
5 2222 1 2
6 2222 1 3
7 2222 0 3
8 2222 1 4
] (delimiter is '\t');

Creator III
2023-09-22
02:53 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, I did not know that peek can be used in same field, which I am currently creating. That is great.
865 Views
