Dynamic Accumulation for different type

[rob_vander]

Hi All,

I came across weird requirement and I have no clue how to solve it.

I have below dataset. I want to have accumulation for each type but it is not full accumulation. I need to go 4 or 3 step back to perform accumulation which is defined in reference data for each Type. Another problem is I have 9-10 type for which there could be any AccumStep between 1 to 10. Hence, accumulation has to be dynamic. I will better explain it with example

// Actual data

Load * Inline [
Type, Sequence, Value
A, 1, 10
A, 2, 20
A, 3, 90
A, 4, 10
A, 5, 30

B, 1, 20
B, 2, 40
B, 3, 80
B, 4, 10

C, 2, 70
C, 3, 30
C, 4, 40

D, 1, 10
D, 2, 30
D, 3, 50
D, 4, 70 ];

// Reference data

Load * Inline [
Type, AccumStep
A, 4
B, 3
C, 2
D, 2 ];

let's say for Type =A and Sequence =5, I need to go back 4 row back because AccumStep in reference data is 4. Hence, Accum value will be,  (20+90+10+30 =150). For Type=A and Sequence =4, Accum will be (10+20+90+10=130). For Type=A and Sequence = 3, there is nothing 4 row back, so start with the first row , so Accum will be (10+20+90=120). For Type=A and Sequence=2, there is nothing 4 row back, so start with the first row , so Accum will be (10+20=30). For first row of any Type it will be actual value, so for Sequence=1, Accum will be 10. This is the example of Type A. Similar logic applies to all other Type considering it's AccumStep in reference data.

Output Reqired:

Type	Sequence	AccumStep	Value	Accum	logic
A	1	4	10	10	10
A	2	4	20	30	(10+20)
A	3	4	90	120	(10+20+90)
A	4	4	10	130	(10+20+90+10)
A	5	4	30	150	(20+90+10+30)
B	1	3	20	20	20
B	2	3	40	60	(20+40)
B	3	3	80	140	(20+40+80)
B	4	3	10	130	(40+80+10)
C	2	2	70	70	70
C	3	2	30	100	(70+30)
C	4	2	40	70	(30+40)
D	1	2	10	10	10
D	2	2	30	40	(10+30)
D	3	2	50	80	(30+50)
D	4	2	70	120	(50+70)

@Vegar @Kushal_Chawda @rwunderlich @MarcoWedel 

[Chanty4u]

Try this 

Data:

LOAD * Inline [

    Type, Sequence, Value

    A, 1, 10

    A, 2, 20

    A, 3, 90

    A, 4, 10

    A, 5, 30

    B, 1, 20

    B, 2, 40

    B, 3, 80

    B, 4, 10

    C, 2, 70

    C, 3, 30

    C, 4, 40

    D, 1, 10

    D, 2, 30

    D, 3, 50

    D, 4, 70

];

Reference:

LOAD * Inline [

    Type, AccumStep

    A, 4

    B, 3

    C, 2

    D, 2

];

DataAccum:

LOAD

    Type,

    Sequence,

    Value,

    if(Sequence = 1, Value, peek(Accum) + Value) as Accum

Resident Data;

Join (DataAccum)

Load

    Type,

    AccumStep

Resident Reference;

Drop Table Data, Reference;

[Vegar]

Hi, it was a tricky one, but try this:

sum(aggr(rangesum(above(total Sum(Value),0,rangemin(rowno(),AccumStep))),Type,Sequence))

[rob_vander]

It's not working @Chanty4u 

[rob_vander]

@Vegar  Thanks for taking time. But, I want to do it in the script. Would you mind sharing script approach?

[Vegar]

Kind of tricky to do it in the script as well. 

I managed to create a script, but you need to know your max AccumStep-value in advance and make one expression per possible AccumStep from 1 to your max. I also assume that each Type have a sequence ranging from 1 to N. In your example Sequence=1 was missing for Type=C.

m_accumstep:

Mapping

Load * Inline [

Type, AccumStep

A, 4

B, 3

C, 2

D, 2 ];

LOAD

  ApplyMap('m_accumstep',Type) as AccumStep,

  pick(rangemin( ApplyMap('m_accumstep',Type), Sequence),

    Value,  //Pick=1 

    rangesum(Value,Peek(Value, -1)),  //Pick=2

    rangesum(Value,Peek(Value, -1),Peek(Value, -2)), //Pick=3

    rangesum(Value,Peek(Value, -1),Peek(Value, -2),Peek(Value, -3)), //Pick=4

  ) as AccValue,

  Type,

  Sequence,

  Value

Inline [

Type, Sequence, Value

A, 1, 10

A, 2, 20

A, 3, 90

A, 4, 10

A, 5, 30

B, 1, 20

B, 2, 40

B, 3, 80

B, 4, 10

C, 1, 30

C, 2, 70

C, 3, 30

C, 4, 40

D, 1, 10

D, 2, 30

D, 3, 50

D, 4, 70 ]

;

[rob_vander]

@Vegar  thanks so much again. But catch is my sequence never start with 1 actually . It has values like 101,102,103.. but they are always in sequence. Also, I would not know my max step in advance as it updates every month so quite dynamic. But thanks again.

[Kushal_Chawda · Accepted solution]

@rob_vander  basically first you need to order your data by Type & Sequence. Then you need below condition for step 4 assuming it is max step so it will work for other step as well.

rangesum(if(Peek(Type,-3)=Type and AccumStep>=4, Peek(Value,-3),0),
     if(Peek(Type,-2)=Type and AccumStep>=3,  Peek(Value,-2),0),
     if(Peek(Type,-1)=Type and AccumStep>=2, Peek(Value,-1),0),Value) 

Now to make above code dynamic based on max AccumStep, you  can use below approach.  Basically you need to generate this whole rangesum statement dynamically.

Data:
Load * Inline [
Type, Sequence, Value
A, 1, 10
A, 2, 20
A, 3, 90
A, 4, 10
A, 5, 30

B, 1, 20
B, 2, 40
B, 3, 80
B, 4, 10

C, 2, 70
C, 3, 30
C, 4, 40

D, 1, 10
D, 2, 30
D, 3, 50
D, 4, 70 ];

Left Join(Data)
Load * Inline [
Type, AccumStep
A, 4
B, 3
C, 2
D, 2 ];

// logic below dynamically generates the entire rangesum statement
Accum_condition:
Load Concat(accum_condition,',') as accum_condition;
Load 'if(Peek(Type,' & (-max_accum_step + IterNo())&')=Type and AccumStep>='& 
      (max_accum_step -(IterNo()-1)) &',Peek(Value,'& (-max_accum_step + IterNo()) &'),0)' as accum_condition
While IterNo() <= max_accum_step-1;
Load max(FieldValue('AccumStep',RecNo())) as max_accum_step
AutoGenerate FieldValueCount('AccumStep');

let vAccumCondition = Peek('accum_condition');

Drop Table Accum_condition;

Final:
Load *,
     RangeSum($(vAccumCondition),Value) as Accum
Resident Data
Order by Type,Sequence;

Drop Table Data;

[MarcoWedel · Accepted solution]

another solution might be:

mapAccumStep:
Mapping
LOAD * Inline [
Type, AccumStep
A, 4
B, 3
C, 2
D, 2
];

table1:
LOAD *,
     FullAccum-If(Seq>AccumStep,Peek(FullAccum,-AccumStep),0) as Accum;
LOAD *,     
     If(Type=Previous(Type),Peek(Seq)+1,1)					as Seq,
     If(Type=Previous(Type),Peek(FullAccum)+Value,Value)	as FullAccum,
     ApplyMap('mapAccumStep',Type)							as AccumStep
Inline [
Type, Sequence, Value
A, 1, 10
A, 2, 20
A, 3, 90
A, 4, 10
A, 5, 30

B, 1, 20
B, 2, 40
B, 3, 80
B, 4, 10

C, 2, 70
C, 3, 30
C, 4, 40

D, 1, 10
D, 2, 30
D, 3, 50
D, 4, 70
];

hope this helps
Marco

[rob_vander]

Works well but I had to sort the data first.