Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Help with merging certain values in field?

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

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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

View solution in original post

3 Replies
Highlighted
Master II
Master II

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

Highlighted
Specialist
Specialist

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

View solution in original post

Highlighted
Creator II
Creator II

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