topic Re: Aggregating values in QlikView

Aggregating values

vijetas42 — Fri, 03 Nov 2017 07:55:27 GMT

Hi All,

I have scenario like if I have same project code starting with Q and B then it show me B and with it's respective status but should show

me aggregated value for both the codes.

Ex.

Current Data
Project	Status	value
BB101	Open	30
QB101	Closed	20
BB102	Open	20
QB103	Pending	10
BB104	Closed	20
QB104	Pending	20

expected output
Project	Status	value
BB101	Open	50
BB102	Open	20
QB103	Pending	10
BB104	Closed	40

Please suggest.

Re: Aggregating values

antoniotiman — Fri, 03 Nov 2017 08:15:18 GMT

May be thsi

LOAD MinString(Project) as Project,MinString(Status) as Status,Sum(value) as Value Group By Common;
LOAD *,Right(Project,4) as Common Inline [
Project Status value
BB101 Open 30
QB101 Closed 20
BB102 Open 20
QB103 Pending 10
BB104 Closed 20
QB104 Pending 20
] (delimiter is spaces);

Re: Aggregating values

Anil_Babu_Samineni — Fri, 03 Nov 2017 08:22:48 GMT

Where you want to do this, in script or UI

may be create straight table

Dimension as project

Expressions are

Firstsortedvalue(status, -aggr(max(value), status))

Sum(value)

Where project in script like

If(left(project,1)='Q', replace(project, 'Q','B'), project) as project

Re: Aggregating values

effinty2112 — Fri, 03 Nov 2017 08:27:19 GMT

Hi Vitjeta,

Try:

[Current Data]:

Load

Right(Project,3) as Suffix,

LOAD * INLINE [

Project, Status, value

BB101, Open, 30

QB101, Closed, 20

BB102, Open, 20

QB103, Pending, 10

BB104, Closed, 20

QB104, Pending, 20

];

Result:

LOAD

MinString(Project) as Project,

Sum(value) as value,

Suffix

Resident

[Current Data] group by Suffix;

Left Join(Result)

LOAD

Project,

Status

Resident [Current Data];

Drop table [Current Data];

gives:

Project	Status	value
BB101	Open	50
BB102	Open	20
QB103	Pending	10
BB104	Closed	40

sort by expression Suffix

Regards

Andrew

Re: Aggregating values

Anonymous — Fri, 03 Nov 2017 08:28:00 GMT

Hi,

Are you sure you want BB101 and QB101 with different status to be aggregated together? I have modified the data slightly so the output makes sense. But if that is what you want, status should not be in the group by statement.

NoConcatenate
CurrentData:
LOAD * INLINE [
Project, Status, Value
BB101, Open, 30
QB101, Open, 20
BB102, Open, 20
QB103, Pending, 10
BB104, Closed, 20
QB104, Pending, 20
];

NoConcatenate
ExpectedOutput:
load
SubField(ProjectConcat,',',1) as Project,
Status,
Value;
load
ProjectCommon,
Status,
sum(Value) as Value,
concat(ProjectFullName,',') as ProjectConcat
group by ProjectCommon,Status;
load
Project as ProjectFullName,
mid(Project,2,len(Project)-1) as ProjectCommon,
Status,Value
Resident
CurrentData;
drop table CurrentData;

Re: Aggregating values

Ralf_Heukäufer — Fri, 03 Nov 2017 08:29:49 GMT

Hello,

you can work with string functions:

[Current Data]:

Load

if(left(Project,1)='Q', replace(Project,'Q','B')) as Project,

from.....

In a next Step you can use a resident load:

Load

Project,

sum(value) as mynewvalue,

resident [Current Data] group by Project;

For the status you need a formula that determines which status will be chosen for several existing values or you can change it in the step where you replace the letters like:

if(left(Project,1)='Q',peek(Status,rowno()-1)) as Status

Re: Aggregating values

ElizaF — Fri, 03 Nov 2017 08:45:43 GMT

Hi,

Try the below solution. With MapSubString function you can solve multiple scenarios.

REPLACE_MAP:

Mapping

LOAD * INLINE [

CHAR, REPLACE

Q,B

];

FINAL:

LOAD

Project,

MapSubString('REPLACE_MAP',LEFT(Project,1))&MID(Project,2,LEN(Project)-1) AS ProjectFinal,

...

Regards,

Eliza

Re: Aggregating values

Anonymous — Fri, 03 Nov 2017 12:10:31 GMT

Hi Vijeta,

Try with below code,

Script:

Data:
LOAD * Inline [
Project,Status,value
BB101,Open,30
QB101,Closed,20
BB102,Open,20
QB103,Pending,10
BB104,Closed,20
QB104,Pending,20
];
New:
Load *, right(Project,3) as Proj
Resident Data;

DROP table Data;

Report:

In straight table use :

Dimensions: Project , Status

Expressions: =aggr(sum(value),Proj)

Re: Aggregating values

Anonymous — Tue, 07 Nov 2017 12:53:39 GMT

Try This,

New:
Load *, mid(Project,index(Project,'Q'),len(Project)) as Proj
Resident Data;

DROP table Data;

Re: Aggregating values

aapurva09 — Mon, 13 Nov 2017 08:21:07 GMT

Hi,

Hope the attached app will help.