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

[Anil_Babu_Samineni]

Are you going to have CountEmp Field or you want to implement this calculation in UI?

[Anil_Babu_Samineni]

Perhaps this?

If(Monthyear = Previous(Monthyear), RangeSum(Peek('Welcome'), Empid), Empid) as Welcome

[rajinikanth]

Thank you anil but no luck

[shiveshsingh]

hi

You can try this using Above func.

refer attached app

[sunny_talwar]

Try this

T1:

LOAD MonthName(Date#(Monthyear, 'MMM-YYYY')) as Monthyear,

Empid

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

];

AggrData:

LOAD Empid,

Monthyear,

If(Empid = Previous(Empid), RangeSum(Peek('CountEmp'), 1), 1) as CountEmp

Resident T1

Order By Empid, Monthyear;

DROP Table T1;

[pradosh_thakur]

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

];

NoConcatenate
T2:
LOAD

*,if(Previous(Empid)=Empid,PEEK(COUNTEMP)+1,1) as COUNTEMP

Resident T1

Order By Empid Asc;

LEFT JOIN(T1)

LOAD * resident T2
order by Monthyear;
drop table T2;