Group data from master table and do calculation IN LOAD SCRIPT

aj0031724 — Tue, 27 Oct 2015 08:48:11 GMT

Team,

We need to group data based on KPI,RNC,UTRANCELL,DATE,site from master table and apply some LOGIC AS BELOW FOR OUTPUT:

if(Previous(RESULT) >4 and RESULT > 4,'Alarmed',
if(-rangesum((RESULT >4),(peek(RESULT,-1)>4),(peek(RESULT,-2)>4),(peek(RESULT,-3)>4),(peek(RESULT,-4)>4),(peek(RESULT,-5)>4),(peek(RESULT,-6)>4))>=4,'Highly utilized',

if(-rangesum((RESULT<3),(peek(RESULT,-1)<3),(peek(RESULT,-2)<3),(peek(RESULT,-3)<3),(peek(RESULT,-4)<3),(peek(RESULT,-5)<3),(peek(RESULT,-6)<3))>=6,'Low utilized',

'Standard'))) as Classification

I am unable to do SO .Can you please suggest /advice how to implement this in load script.

I nned the classification to be grouped based on KPI,RNC,UTRANCELL,DATE,site as highlighted in color sets.

data sets example:

_KPI_NAME_GenericKpisActivatedByUser	UTRANCELL	RNC	DATE	SITE	RESULT	CLASSIFICATION
COMB CS DROPS	ASESP22	DPP1R01	06.12.14	ASESP	3
COMB CS DROPS	ASESP22	DPP1R01	06.13.14	ASESP	4
COMB CS DROPS	ASESP22	DPP1R01	06.14.14	ASESP	11
COMB CS DROPS	ASESP22	DPP1R01	06.15.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.16.14	ASESP	7
COMB CS DROPS	ASESP22	DPP1R01	06.17.14	ASESP	5
COMB CS DROPS	ASESP22	DPP1R01	06.18.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.19.14	ASESP	8
COMB CS DROPS	ASESP22	DPP1R01	06.20.14	ASESP	7
COMB CS DROPS	ASESP22	DPP1R01	06.21.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.22.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.23.14	ASESP	10
COMB CS DROPS	ASESP22	DPP1R01	06.24.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.25.14	ASESP	19
COMB CS DROPS	ASESP22	DPP1R01	06.26.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.27.14	ASESP	4
COMB CS DROPS	ASESP22	DPP1R01	06.28.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.29.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.30.14	ASESP	22
COMB CS DROPS	ASESP22	DPP1R01	07.01.14	ASESP	3
COMB CS DROPS	ATACM22	DPP1R01	06.12.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.13.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.14.14	ATACM	11
COMB CS DROPS	ATACM22	DPP1R01	06.15.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.16.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	06.17.14	ATACM	12
COMB CS DROPS	ATACM22	DPP1R01	06.18.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.19.14	ATACM	7
COMB CS DROPS	ATACM22	DPP1R01	06.20.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.21.14	ATACM	1
COMB CS DROPS	ATACM22	DPP1R01	06.22.14	ATACM	4
COMB CS DROPS	ATACM22	DPP1R01	06.23.14	ATACM	2
COMB CS DROPS	ATACM22	DPP1R01	06.24.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.25.14	ATACM	7
COMB CS DROPS	ATACM22	DPP1R01	06.26.14	ATACM	15
COMB CS DROPS	ATACM22	DPP1R01	06.27.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.28.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	06.29.14	ATACM	4
COMB CS DROPS	ATACM22	DPP1R01	06.30.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	07.01.14	ATACM	9

Re: Group data from master table and do calculation IN LOAD SCRIPT

jonathandienst — Tue, 27 Oct 2015 09:10:18 GMT

The syntax of your expression looks OK, but it will assume that the data is correctly sorted.

Re: Group data from master table and do calculation IN LOAD SCRIPT

aj0031724 — Tue, 27 Oct 2015 09:28:02 GMT

Dear Jonathan,

Yes the master table contains data as shown above in sorted order.

Now I need to do classification grouped by KPI,RNC,UTRANCELL,DATE,site .

How can I use group by in load for the above classification .

Can you please advise?

Re: Group data from master table and do calculation IN LOAD SCRIPT

aj0031724 — Wed, 28 Oct 2015 06:21:56 GMT

Dear Team,

Can you please guide me with the above ?

topic Re: Group data from master table and do calculation IN LOAD SCRIPT in QlikView

Group data from master table and do calculation IN LOAD SCRIPT

Re: Group data from master table and do calculation IN LOAD SCRIPT

Re: Group data from master table and do calculation IN LOAD SCRIPT

Re: Group data from master table and do calculation IN LOAD SCRIPT