Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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

View solution in original post

10 Replies
Anonymous
Not applicable
Author

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
Author

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

Not applicable
Author

Hi Nima,

Please see attached sample may help you.

Cheers.

Anonymous
Not applicable
Author

Correct, thanks

Not applicable
Author

Thanks Michael,

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

Thanks

Nima

Not applicable
Author

Hello Micheal,

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

Thanks

Anonymous
Not applicable
Author

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
Author

Hello,

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

What can we do?

Thanks

Anonymous
Not applicable
Author

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