topic Re: Rows calculatein in script in QlikView

Rows calculatein in script

Thu, 27 Oct 2011 19:30:07 GMT

Hi All,

I'm trying to create a calculation in script like this:

Here is my table:

Countries	Sales	Forcast
Canada	40	100
US	45	50
Spain	32	40
CAnada + US	85	150

What I want is to create the last line in the script.

Thanks

Re: Rows calculatein in script

Anonymous — Fri, 28 Oct 2011 00:40:19 GMT

Nima,

use this concatenate load:

table:
LOAD
Countries,
Sales,
Forecast
FROM <source>;

CONCATENATE (table) LOAD
'Canada + US' as Countries,
sum(Sales) as Sales,
sum(Forecast) as Forecast
RESIDENT table

WHERE match(Countries,'Canada','US');

EDIT:added "WHERE" line

Rows calculatein in script

Fri, 28 Oct 2011 02:04:28 GMT

where MATCH(Countries,'Canada','US')

Re: Rows calculatein in script

Fri, 28 Oct 2011 06:14:16 GMT

Hi Nima,

Please see attached sample may help you.

Cheers.

Rows calculatein in script

Anonymous — Fri, 28 Oct 2011 10:02:53 GMT

Correct, thanks

Rows calculatein in script

Fri, 28 Oct 2011 13:13:55 GMT

Thanks Michael,

I will try it with my real data , hope it works.

Thanks

Nima

Rows calculatein in script

Fri, 28 Oct 2011 13:27:58 GMT

Hello Micheal,

Another question for you. If we want to do a calculation like Canada - Spain , how do we do?

Thanks

Rows calculatein in script

Anonymous — Fri, 28 Oct 2011 13:34:23 GMT

Nima,

It will be similar, the additional piece of code:

CONCATENATE (table) LOAD

'Canada + Spain' as Countries,

sum(Sales) as Sales,

sum(Forecast) as Forecast

RESIDENT table

WHERE match(Countries,'Canada','Spain');

Rows calculatein in script

Fri, 28 Oct 2011 13:47:05 GMT

Hello,

I mean if we want to Subtract canada from Spain : Canada " - " Spain

What can we do?

Thanks

Rows calculatein in script

Anonymous — Fri, 28 Oct 2011 14:01:02 GMT

Nima,

Why would you want to do it in the script? This is something that more often makes sense (and much more flexible and easy) on the front-end.
Anyway, if it must be in the script for some reason, try this:
CONCATENATE (table) LOAD
'Canada - Spain' as Countries,
sum(if(Countries='Canada', Sales, - Sales) as Sales,
sum(if(Countries='Canada', Forecast, -Forecast) as Forecast
RESIDENT table
WHERE match(Countries,'Canada','Spain');

Rows calculatein in script

Fri, 28 Oct 2011 14:55:26 GMT

Michael,

I updated the discussion and add the real table as attachment.

The reason I need to do it in script is : If I have two measures for example Weight and Dollar and I want to the calculation , as far as I know it's not possible.If you take a look at the attached file you will see that I have weight in the report and I did my calculation in expression , like Sales+ Forecast and it works for just Weight but if I have Dollar in the report as another measure there is no way to do it.

If you have any solution , I would appreciate.

Thanks,

Nima