Grouping Data

[#1]

Hello,

I have data for individual sales reps and I would like to group them by region.

Rep	Sales
Jeff	$3,000
Mark	$4500
John	$1800
Steve	$6000
Ryan	$4400
Lou	$3200
Frank	$2900
James	$4500
Sam	$4200
Tim	$1300
Jake	$4800

Instead of showing names I would like Qlikview to show regions.  I would like to group Jeff, Mark and John in region 1, Steve, Ryan and Lou in Region 2, Frank, James and Sam in region 3 and Tim and Jake in region 4.  Is there a way I can do this in the script editor?

Script editor shows the following:

;

LOAD Rep,

     Sales

Is there a way I can put under Rep something along the lines of Jeff, Mark, John = Region 1, John, Steve, Ryan = Region 2, etc.?  Thanks.

[tresesco]

Try using map like:

MappingTab:

Mapping Load * Inline [

XX, YY

Jeff, R1

Mark, R1

John, R1

Steve, R2

....

] ;

Table:

Load

          ApplyMap('MappingTab' , Rep) as Region,

          Sales

From <>;

[vikasmahajan]

Not there in source data ?

[#4]

My region list box in Qlikview is not showing any data.

My Main Tab shows this:

ApplyMap('MappingTab' , Rep) as Region,

     Sales

FROM

My Mapping Tab shows this:

Load * Inline [

XX, YY

Jeff, R1

Mark, R1

John, R1
Steve, R2

Ryan, R2

Lou, R2

Frank, R3

James, R3

Sam, R3

Tim, R4

Jake, R4

] ;

Am I messing up somewhere?  Also to make things more confusing is there a way so that I can show the reps as individuals and by region?

[jonathandienst]

Hi

The mapping load needs to be before the ApplyMap and it needs a "Mapping LOAD * Inline" rather than just LOAD. The mapping table must be named and the ApplyMap must use this name.

HTH

Jonathan

[Anonymous]

Hi Michael,

Please find the attachments.I have done as of your requirement.

[#7]

My mapping tab is named Mapping.  I currently have the below but it still isn't working.

;Mapping Load * Inline"

ApplyMap('Mapping' , Rep) as Region

     Sales

FROM

[jonathandienst]

Please post the full script from the Mapping Load through to the load with ApplyMap.

[preminqlik]

Load Rep,

Sales,

if(

wildmatch(Rep,'Jef','Mark','Jhon'),'Region1',if(

wildmatch(Rep,'Steve','ryan','Lou'),'Region2',if(

wildmatch(Rep,'Frank','James','Sam'),'Region3',if(

wildmatch(Rep,'Tim','Jake'),'Region4'))))                              as                         Region
from

XXXXX

[tresesco]

PFA