Alternate to Rangesum(Above/Below) using Rangesum & Peek in load script

[Lokesh_5045]

Hi Qlik Experts,

I have a data like below.
Load * Inline [
ID, month, val1, val2
A, Jan, 10, 2
A, Feb, 20, 4
A, Mar, 30, 2
A, Apr, 40, 1
A, May, 50, 3
A, Jun, 60, 2
A, Jul, 70, 3
A, Aug, 80, 2
B, Jan, 90, 2
B, Feb, 100, 4
B, Mar, 110, 2
B, Apr, 120, 1
B, May, 130, 3
B, Jun, 140, 2
B, Jul, 150, 3
B, Aug, 160, 2
];

I need to calculate new field. If Val2 = 3, then I need to sum next 3 values of Val1. I have written script like below.

load *,
If(val2 = 1, RangeSum(val1),
If(val2 = 2, RangeSum(val1, Peek('val1',RowNo(),'data')),
If(val2 = 3, RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data')),
If(val2 = 4, RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data'), Peek('val1',RowNo()+2,'data')))))) as Newfield
Resident data;

The problem here is, I need to do rangesum for each ID seperately. The output (where I am getting problem) is 

The expected output for those last A records.

A        Jul           70            3          150
A        Aug         80            2          80

Newfield of A shouldn't consider B records. Can anyone help me in this?

If we use RowNo(), this problem persists. If there's any alternative way to solve this, Please suggest me.

[MayilVahanan]

Hi

Try like below

data:
Load * Inline [
ID, month, val1, val2
A, Jan, 10, 2
A, Feb, 20, 4
A, Mar, 30, 2
A, Apr, 40, 1
A, May, 50, 3
A, Jun, 60, 2
A, Jul, 70, 3
A, Aug, 80, 2
B, Jan, 90, 2
B, Feb, 100, 4
B, Mar, 110, 2
B, Apr, 120, 1
B, May, 130, 3
B, Jun, 140, 2
B, Jul, 150, 3
B, Aug, 160, 2
];

InterTable:
load *, Peek('ID',RowNo(),'data') as NextID, Peek('ID',RowNo()+1,'data') as Next2ID, Peek('ID',RowNo()+2,'data') as Next3ID
,If(val2 = 1, RangeSum(val1),
If(val2 = 2, RangeSum(val1, Peek('val1',RowNo(),'data')),
If(val2 = 3, RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data')),
If(val2 = 4, RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data'), Peek('val1',RowNo()+2,'data')))))) as OrgNewfield
Resident data;

load *,
If(val2 = 1, RangeSum(val1),
If(val2 = 2, RangeSum(val1, if(ID = NextID, Peek('val1',RowNo(),'data'))),
If(val2 = 3, RangeSum(val1, if(ID = NextID, Peek('val1',RowNo(),'data')), if(ID = Next2ID, Peek('val1',RowNo()+1,'data'))),
If(val2 = 4, RangeSum(val1, if(ID = NextID, Peek('val1',RowNo(),'data')), if(ID = Next2ID, Peek('val1',RowNo()+1,'data')), if(ID = Next3ID, Peek('val1',RowNo()+2,'data'))))))) as Newfield
Resident InterTable;

DROP Table data, InterTable;
Drop Field NextID, Next2ID, Next3ID;

[Aditya_Chitale]

Try this:

data:

Load * Inline [

ID, month, val1, val2

A, Jan, 10, 2

A, Feb, 20, 4

A, Mar, 30, 2

A, Apr, 40, 1

A, May, 50, 3

A, Jun, 60, 2

A, Jul, 70, 3

A, Aug, 80, 2

B, Jan, 90, 2

B, Feb, 100, 4

B, Mar, 110, 2

B, Apr, 120, 1

B, May, 130, 3

B, Jun, 140, 2

B, Jul, 150, 3

B, Aug, 160, 2

];

NoConcatenate

test2:

load *,

If(val2 = 1 and ID = peek('ID',rowno()+1,'data'), RangeSum(val1),

If(val2 = 2 and ID = peek('ID',rowno()+1,'data'), RangeSum(val1, Peek('val1',RowNo(),'data')),

If(val2 = 3 and ID = peek('ID',rowno()+1,'data'), RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data')),

If(val2 = 4 and ID = peek('ID',rowno()+1,'data'), RangeSum(val1, Peek('val1',RowNo(),'data'), Peek('val1',RowNo()+1,'data'), Peek('val1',RowNo()+2,'data')),

    if(SecondLastEntryPerGroup = '1', RangeSum(val1, Peek('val1',RowNo(),'data')),

    if(LastEntryPerGroup = '1', RangeSum(val1))))))) as Newfield;

load *,

if(ID = peek('ID',recno(),'data') and ID <> peek('ID',recno()+1,'data'),1,0) as SecondLastEntryPerGroup,

if(ID <> peek('ID',recno(),'data'),1,0) as LastEntryPerGroup

Resident data;

drop table data;

Output:

Regards,

Aditya