Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
...From the Mapping Load (including the name) through to the load with ApplyMap....
You need to give the table a name. That is then followed by the mapping load.
RegionMap:
MAPPING LOAD INLINE ....
applymap('RegionMap', Rep)
-Rob
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;