topic Re: Rolling up values in a field in QlikView

Rolling up values in a field

Mon, 28 Sep 2015 15:57:37 GMT

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

Re: Rolling up values in a field

swuehl — Mon, 28 Sep 2015 16:07:47 GMT

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

];

Re: Rolling up values in a field

petter — Mon, 28 Sep 2015 16:07:59 GMT

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.

Re: Rolling up values in a field

miguelbraga — Mon, 28 Sep 2015 16:14:57 GMT

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

Re: Rolling up values in a field

Wed, 30 Sep 2015 18:52:42 GMT

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

Re: Rolling up values in a field

swuehl — Wed, 30 Sep 2015 21:30:27 GMT

You want something like this?

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