topic Re: Grouping Data in QlikView

Grouping Data

Mon, 10 Mar 2014 10:48:38 GMT

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.

Re: Grouping Data

tresB — Mon, 10 Mar 2014 11:03:05 GMT

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

Re: Grouping Data

vikasmahajan — Mon, 10 Mar 2014 11:08:24 GMT

Not there in source data ?

Re: Grouping Data

Mon, 10 Mar 2014 11:24:13 GMT

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?

Re: Grouping Data

jonathandienst — Mon, 10 Mar 2014 11:38:34 GMT

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

Re: Grouping Data

Anonymous — Mon, 10 Mar 2014 11:40:51 GMT

Hi Michael,

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

Re: Grouping Data

Mon, 10 Mar 2014 11:57:03 GMT

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

Re: Grouping Data

jonathandienst — Mon, 10 Mar 2014 12:02:28 GMT

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

Re: Grouping Data

preminqlik — Mon, 10 Mar 2014 12:04:50 GMT

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

Re: Re: Grouping Data

tresB — Mon, 10 Mar 2014 12:07:25 GMT

PFA

Re: Grouping Data

Mon, 10 Mar 2014 12:47:43 GMT

Mapping Load * Inline [

Rep, Region

Jeff, R1

Mark, R1

John, R1

Steve, R2

Ryan, R2

Lou, R2

Frank, R3

James, R3

Sam, R3

Tim, R4

Jake, R4

];

Re: Grouping Data

jonathandienst — Mon, 10 Mar 2014 14:46:18 GMT

...From the Mapping Load (including the name) through to the load with ApplyMap....

Re: Grouping Data

rwunderlich — Mon, 10 Mar 2014 14:57:01 GMT

You need to give the table a name. That is then followed by the mapping load.

RegionMap:

MAPPING LOAD INLINE ....

applymap('RegionMap', Rep)

-Rob

Re: Grouping Data

Mon, 10 Mar 2014 15:15:32 GMT

load * inline
[
Rep, Sales
Jeff, 3000
Mark, 4500
John, 1800
Steve, 6000
Ryan, 4400
Lou, 3200
Frank, 2900
James, 4500
Sam, 4200
Tim, 1300
Jake, 4800
];

T2:
LOAD
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 ,
Sales as SL
Resident T1;

T3:
Load
Region,
sum(SL) as Region_SL
resident T2
group by Region;