Rolling up values in a field

[#1]

Sorry if this has been asked before but I was unable to search for it.

I have two columns :

Region                Sales

America AB         10000

America AS          500

America AT          400

Asia TY               6000

Asia AS               500

Africa CS             4000

Quite simply I want to roll up the region values so that for instance anything like 'America*' rolls up into 'America', anything like 'Asia*' rolls up into 'Asia' and so on .... So in this example I will be left with only 3 rows of data. One for America, Asia and Africa.

Thanks

[swuehl · Accepted solution]

Try something like

LOAD *,

          Pick(WildMatch(Region, 'America*','Asia*','Africa*')+1,'Other', 'America', 'Asia', 'Africa') as NewRegion;

LOAD * INLINE [

Region,                Sales

America AB,         10000

America AS,          500

America AT,          400

Asia TY,               6000

Asia AS,               500

Africa CS,             4000

];

[petter]

If you need to do it in a load script:

LOAD

     Continent,

     Sum( Sales )

GROUP BY

     Continent;

LOAD

     Pick( WildMatch( Region , 'America *' , 'Asia *' , 'Africa *' ) + 1  , 'Other', 'America', 'Asia' , 'Africa' ) AS Continent

     Sales

FROM    // OR SQL SELECT ....

     ....

;

If you want do keep the level of detail in your application for other analysis or reporting purposes only do this in the load script:

LOAD

     Pick( WildMatch( Region , 'America *' , 'Asia *' , 'Africa *' ) + 1  , 'Other', 'America', 'Asia' , 'Africa' ) AS Continent

     Sales

FROM    // OR SQL SELECT ....

     .....

;

And then select the Continent as a dimension and use Sum( Sales ) as expression in a chart.

[miguelbraga]

Hi Sid_Smith

You need to create two new fields from Region like this:

SubField(Region, ' ', 1) as Region,
SubField(Region, ' ', 2) as RegionID,

Then you only need to create a pivot table to colapse the values to get your desired results.

See the qvw attached file.

Miguel

[#5]

Hi this works perfectly. Thank you.

Just another question if I had other values also but didn't want them to be amended ie should be displayed as is then how I should edit my script ?

Currently it will only displayed the rolled up values and nothing else

[swuehl]

You want something like this?

  Pick(WildMatch(Region, 'America*','Asia*','Africa*')+1, Region , 'America', 'Asia', 'Africa') as NewRegion;