topic Re: Accumulation in Script in QlikView

Accumulation in Script

rajinikanth — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Accumulation in Script

mdmukramali — Thu, 28 Dec 2017 16:51:40 GMT

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;

Re: Accumulation in Script

Anil_Babu_Samineni — Thu, 28 Dec 2017 16:56:13 GMT

Perhaps this?

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

Re: Accumulation in Script

sunny_talwar — Thu, 28 Dec 2017 17:09:57 GMT

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

Re: Accumulation in Script

Anil_Babu_Samineni — Thu, 28 Dec 2017 17:11:09 GMT

Yes, I think so.

Re: Accumulation in Script

sunny_talwar — Thu, 28 Dec 2017 17:15:52 GMT

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

Re: Accumulation in Script

sunny_talwar — Thu, 28 Dec 2017 17:18:44 GMT

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

Re: Accumulation in Script

alis2063 — Thu, 28 Dec 2017 18:11:48 GMT

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

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

Re: Accumulation in Script

sunny_talwar — Thu, 28 Dec 2017 18:36:11 GMT

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

Re: Accumulation in Script

rajinikanth — Thu, 28 Dec 2017 18:47:46 GMT

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

Re: Accumulation in Script

Anil_Babu_Samineni — Fri, 29 Dec 2017 01:06:19 GMT

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

Re: Accumulation in Script

Anil_Babu_Samineni — Fri, 29 Dec 2017 01:34:37 GMT

Perhaps this?

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

Re: Accumulation in Script

rajinikanth — Fri, 29 Dec 2017 03:49:43 GMT

Thank you anil but no luck

Re: Accumulation in Script

shiveshsingh — Fri, 29 Dec 2017 06:16:15 GMT

You can try this using Above func.

refer attached app

Re: Accumulation in Script

sunny_talwar — Fri, 29 Dec 2017 17:37:02 GMT

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;

Re: Accumulation in Script

pradosh_thakur — Fri, 29 Dec 2017 17:37:58 GMT

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;