Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Rows calculatein in script

Hi All,

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

Here is my table:

CountriesSalesForcast
Canada40100
US4550
Spain3240
CAnada + US85150

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

Thanks

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Rows calculatein in script

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');

10 Replies
mov
Esteemed Contributor III

Re: Rows calculatein in script

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

Not applicable

Rows calculatein in script

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

Not applicable

Re: Rows calculatein in script

Hi Nima,

Please see attached sample may help you.

Cheers.

mov
Esteemed Contributor III

Rows calculatein in script

Correct, thanks

Not applicable

Rows calculatein in script

Thanks Michael,

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

Thanks

Nima

Not applicable

Rows calculatein in script

Hello Micheal,

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

Thanks

mov
Esteemed Contributor III

Rows calculatein in script

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');

Not applicable

Rows calculatein in script

Hello,

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

What can we do?

Thanks

mov
Esteemed Contributor III

Rows calculatein in script

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');

Community Browser