Manipulating filed value in pivot table

[#1]

I need some help in manipulating values at chart level. Below is the scenario.

I have two columns a pivot table in the below format

Country   Sum(sales)

IN             1000

India         2000

US            3000

USA          4000

Ideally both IN,INDIA  mean same and the same with US,USA

Now i need the data in below format.

Country  Sum(Sales)

IN           3000

US         7000

Any suggestions would be greatly helpful !

[evan_kurowski · Accepted solution]

Hello vr28042011,

Use an expression as your chart calculated dimension.

=Upper(Left(Country,2))  should produce a rollup that merges the different lengths and cases.

[sushil353]

Hi,

The easy and effective way is to use mapping table for all such scenario's...

in your script add a mapping table.

country:

Mapping load * inline

[

C_Full,C_Initial

India,IN

USA,US

....

.

];

then in your table you can add additional field for ur requirement.

load

..

...

..

Applymap('country',countryfield) as countryinitial,

from

source

HTH

Sushil

[sushil353]

Hi Evan,

This solution might fails if there is Indonesia is also there..

[aveeeeeee7en]

Hi

You can get the desired Result either by using ApplyMap() or Match() function.

ApplyMap():

As Sushil suggested.

Match() Function:

If(Match(Country,'IN','India'),'IN',

If(Match(Country,'US','USA'),'US'))

See the Attachment.

Regards

Aviral Nag

[MayilVahanan]

Hi

By using Mapping Load and Apply Map in the script will help you.

Hope it helps

[evan_kurowski]

Hello sushil,

It is true that if you add entries to this table in which distinction is lost based on the first two characters, you will need secondary identifiers.

In which case, as you mentioned, you can form an alias table mapping full and alternate spellings of all identified countries and resolve them to a unique set of country codes, such as the one here
2-letter, 3-letter, country codes for all countries in the world

If in your alias table you had the entries for:
Indonesia, ID

India, IN

etc...

As long as the two-letter codes are unique per country, your Sum sales will categorize properly.

[sujeetsingh]

You can use match or wild match function

[#9]

Hi Evan,

           Thanks a lot for the help. It really worked for my requirement.

[#10]

Dear All,

        Thanks a lot for your help. Many suggestions are helpful. But i needed something which can be done at chart level than script level. Evan solution has worked for my requirement.