topic Re: Manipulating filed value in pivot table in QlikView

Manipulating filed value in pivot table

Thu, 04 Sep 2014 05:43:51 GMT

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 !

Re: Manipulating filed value in pivot table

evan_kurowski — Thu, 04 Sep 2014 05:47:41 GMT

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.

Re: Manipulating filed value in pivot table

sushil353 — Thu, 04 Sep 2014 05:49:34 GMT

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

Re: Manipulating filed value in pivot table

sushil353 — Thu, 04 Sep 2014 05:50:51 GMT

Hi Evan,

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

Re: Manipulating filed value in pivot table

aveeeeeee7en — Thu, 04 Sep 2014 06:01:39 GMT

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

Re: Manipulating filed value in pivot table

MayilVahanan — Thu, 04 Sep 2014 06:06:27 GMT

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

Hope it helps

Re: Manipulating filed value in pivot table

evan_kurowski — Thu, 04 Sep 2014 06:08:41 GMT

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.

Re: Manipulating filed value in pivot table

sujeetsingh — Thu, 04 Sep 2014 06:16:29 GMT

You can use match or wild match function

Re: Manipulating filed value in pivot table

Thu, 04 Sep 2014 06:31:29 GMT

Hi Evan,

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

Re: Manipulating filed value in pivot table

Thu, 04 Sep 2014 06:40:34 GMT

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.

Re: Manipulating filed value in pivot table

Thu, 04 Sep 2014 06:42:46 GMT

Hi Sushil,

the column i gave is a sample one. In my case i have only few values in the column and the scenario you mentioned may not occur in my requirement. Thanks for pointing though. . will be cautious when using elsewhere 😉