Help with merging certain values in field?

[aaronnayan]

Hi Guys,

How do i merge to values in afield together? In doing so I want their sales to combine together also?

Fields:

[Salesteam]

Sum(sales)

Sum(Margin)

SalesTeam          Sales           Margin

Alice                    500                  40

Joe                      400                  30    

Rob                     300                  20    

Hardy                  150                  80    

Bob                      80                    80

Tom                     90                    10

However Tom and Bob need to be in a team and Rob and Joe.. (Please see below)

SalesTeam          Sales           Margin

Alice                    500                  40   

Rob/Joe               700                  50    

Hardy                  150                  80    

Bob/Tom              170                 90

How would i do this? an if statment? IF(SalesTeam = Rob and Joe?). I need it to aggregate the values for sales and margin when it joins the two together.

Thanks,

Aaron

[trdandamudi]

Please see below:

Data:
Load
IF(SalesTeam='Rob' Or SalesTeam='Joe','Rob/Joe',
IF(SalesTeam='Bob' Or SalesTeam='Tom','Bob/Tom',SalesTeam)) as SalesTeam,
Sales,
Margin;
Load * Inline [
SalesTeam, Sales, Margin
Alice, 500, 40
Joe, 400, 30 
Rob, 300, 20 
Hardy, 150, 80 
Bob, 80, 80
Tom, 90, 10
];

[andrey_krylov · Accepted solution]

Hi,  Aaron. If there are just a few statements, try a calculated dimension

=If(Match([SalesTeam], 'Rob', 'Joe'), 'Rob/Joe',

     If(Match([SalesTeam], 'Bob', 'Tom'), 'Bob/Tom', [SalesTeam]))

[giovanneb]

Hi, Try this

Sales:

NoConcatenate

LOAD

SalesTeam,

sum(Sales) as Sales,

sum(Margin) as Margin

Group by SalesTeam;

LOAD

if(SalesTeam = 'Joe', 'Rob/Joe',

if(SalesTeam =  'Rob', 'Rob/Joe',

if(SalesTeam =  'Bob', 'Bob/Tom',

if(SalesTeam =  'Tom', 'Bob/Tom',SalesTeam)))) AS SalesTeam,

Sales,

Margin

Resident tmpSales;

Reguards