Accumulation in Script

[rajinikanth]

Hi All, I have data as below

T1:

Load * inline

[

Monthyear, Empid

Jan-2016, a1

Jan-2016, b1

Jan-2016, c1

Feb-2016, a1

Feb-2016, b1

Feb-2016, c1

Mar-2016, a1

Mar-2016,b1

Mar-2016, d1

Mar-2016, e1

];

I'm trying to calculated the accumulated employee count Monthyear wise. I need accumulation in the script as below.

No of employees in jan is 3

No of employees in feb is 3  and accumulated count is 6.(jan + feb)

No of employees in mar is 4 and accumulated count is 10 (jan + feb + mar)

expected output

New field

jan 3

feb 6

mar 10

[mdmukramali]

Hi,

Try the below working script.

T1:

Load *,

Date#(Monthyear,'MMM-YYYY') as MONTHYEAR

inline

[

Monthyear, Empid

Jan-2016, a1

Jan-2016, b1

Jan-2016, c1

Feb-2016, a1

Feb-2016, b1

Feb-2016, c1

Mar-2016, a1

Mar-2016,b1

Mar-2016, d1

Mar-2016, e1

];

LOAD

MONTHYEAR,

RangeSum(Peek('CountEmp'),Count_Emp) as CountEmp;

LOAD MONTHYEAR,

Count(Empid) as Count_Emp

Resident T1

Group By MONTHYEAR

Order By MONTHYEAR Asc;

[Anil_Babu_Samineni]

Perhaps this?

LOAD Monthyear, RangeSum(Peek('CountEmp'),Count(Empid)) as CountEmp Resident T1 Group By Monthyear;

[sunny_talwar]

Never tried to combine GroupBy with Peek, does it work?

[Anil_Babu_Samineni]

Yes, I think so.

[sunny_talwar]

Cool, I am going to try it out. Thanks for showing this

[sunny_talwar]

It did work... thanks Anil!! It feels awesome to learn new things

[alis2063]

Could you please explain the below that how does it work?

RangeSum(Peek('CountEmp'),Count_Emp) as CountEmp;

[sunny_talwar]

It's just summing the value from row above to the value in the current row to get you accumulated value

[rajinikanth]

Hi Anil, Thank you for the solution with your solution i got the output as below,

CountEmp	Monthyear	Empid
3	01-01-2016	a1
3	01-01-2016	b1
3	01-01-2016	c1
6	01-02-2016	a1
6	01-02-2016	b1
6	01-02-2016	c1
10	01-03-2016	a1
10	01-03-2016	b1
10	01-03-2016	d1
10	01-03-2016	e1

The actual outcome should be as below

CountEmp	Monthyear		Empid
1	01-01-2016		a1
1	01-01-2016		b1
1	01-01-2016		c1
2	01-02-2016		a1
2	01-02-2016		b1
2	01-02-2016		c1
3	01-03-2016		a1
3	01-03-2016		b1
2	01-03-2016		c1
1	01-03-2016		d1
1	01-03-2016		e1

In March  a1=3 and b1=3 as its accumulation in feb and jan is 2 and 1

In March d1 and e1 are new so they will have accumulation as 1

In march c1  accumulation is 2

a1+b1+c1+d1+e1   = 3+3 +2+1+1 =10