topic Help with merging certain values in field? in QlikView

Help with merging certain values in field?

aaronnayan — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help with merging certain values in field?

trdandamudi — Tue, 04 Sep 2018 13:08:58 GMT

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

Re: Help with merging certain values in field?

andrey_krylov — Tue, 04 Sep 2018 13:15:52 GMT

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

Re: Help with merging certain values in field?

giovanneb — Tue, 04 Sep 2018 13:16:15 GMT

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