Aggregating values

[vijetas42]

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.

[antoniotiman]

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); 

[Anil_Babu_Samineni]

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

[effinty2112]

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

[Anonymous]

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;

[Ralf_Heukäufer]

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

[ElizaF]

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

[Anonymous]

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)

[Anonymous]

Try This,

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


DROP table Data;

[aapurva09 · Accepted solution]

Hi,

Hope the attached app will help.