topic Re: AGREGATION in QlikView

AGREGATION

Mon, 17 Feb 2014 09:45:58 GMT

Good morning, please, i need some help on this.

How can i get, with a table of this kind of data:

MARKET	STATE_NAME	Sum
spain	Andalucia	1
spain	Madrid	3
spain	Valencia	5
spain	Galicia	7
portugal	Porto	2
portugal	Lisboa	1
portugal	Setubal	2

Obtein an objct table like this:

STATE_NAME	Sum
Andalucia	1
Madrid	3
Valencia	5
Galicia	7
portugal	5

Thanks a lot!

Belén

Re: AGREGATION

luis_pimentel — Mon, 17 Feb 2014 09:50:39 GMT

The easiest way it will be in your load script, do something like:

LOAD

MARKET,

STATE_NAME,

IF (MARKET = 'Portugal', 'Portugal', STATE_NAME) AS STATE_NAME_AUX

FROM yourfile...

and then use STATE_NAME_AUX as a dimension, and sum(yourvalue) as a expression.

Hope that helps.

Re: AGREGATION

MK_QSL — Mon, 17 Feb 2014 09:52:11 GMT

Can you share your application or sample file?

Not fully understood your requirement from your question

Re: AGREGATION

Mon, 17 Feb 2014 10:09:53 GMT

What i want is to do it from the chart, in properties Dimension. As a calculated dimensión, if posible.

Thanks

Re: AGREGATION

Mon, 17 Feb 2014 10:12:56 GMT

Hi,

Use the below calculated dimension in your chart.

=if(MARKET = 'spain', STATE_name,MARKET)

Re: AGREGATION

Mon, 17 Feb 2014 13:57:36 GMT

Thanks Venugo, That fits with my problem.

Other question... it is possible, also with other file, ( also in the dimensión):

From this data:

MARKET	STATE_NAME	Sum	Channel
spain	Andalucia	1	C.C
spain	Madrid	3	Other
spain	Valencia	5	On line
spain	Galicia	7	Other
portugal	Porto	2	On line
portugal	Lisboa	1	C.C
portugal	Setubal	2	Other

Get a table object like this? Taking also On line and Portugal inside the table whith the state_name

STATE_NAME	Sum
Andalucia	1
Madrid	3
Galicia	7
On line	7
portugal	3

Thanks a lot!

Belén

Re: AGREGATION

Mon, 17 Feb 2014 14:24:59 GMT

Hi,

Try with below in your expression.

Sum({<Channel -= {'On line'}>}Sum)

Re: AGREGATION

Mon, 17 Feb 2014 15:05:51 GMT

Is not working.....

=if(MARKET = 'spain', STATE_name,MARKET) Sum({<Channel -= {'On line'}>}Sum)

all together?

Re: AGREGATION

MK_QSL — Mon, 17 Feb 2014 15:12:35 GMT

Below should be in script

=if(MARKET = 'spain', STATE_name,MARKET as STATE_name

Below as table or chart expression.

Sum({<Channel -= {'On line'}>}Sum)

Re: AGREGATION

Mon, 17 Feb 2014 15:54:19 GMT

Hi,

Use Dimension as : =if(MARKET = 'spain', STATE_name,MARKET)

and Expression could be : Sum({<Channel -= {'On line'}>}Sum)

Re: AGREGATION

Mon, 17 Feb 2014 17:21:44 GMT

I am trying to do something like this in Dimension..

if ([Market]=('Portugal'),'Portugal',[Region]) and if([Channel]=('on line'),'On line',[Region])

But this not works

Re: AGREGATION

Mon, 17 Feb 2014 18:19:47 GMT

Hi,

Thy the below expression as Calculated dimension:

=if(MARKET = 'spain' and Channel <> 'On line', STATE_NAME,if(MARKET <> 'spain' and Channel <> 'On line', MARKET, Channel))

Expression could be Sum(Sum)

Re: AGREGATION

Tue, 18 Feb 2014 14:18:34 GMT

Thanks a lot!