Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
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
where MATCH(Countries,'Canada','US')
Hi Nima,
Please see attached sample may help you.
Cheers.
Correct, thanks
Thanks Michael,
I will try it with my real data , hope it works.
Thanks
Nima
Hello Micheal,
Another question for you. If we want to do a calculation like Canada - Spain , how do we do?
Thanks
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');
Hello,
I mean if we want to Subtract canada from Spain : Canada " - " Spain
What can we do?
Thanks
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');