SCRIPT

[Carlaf_93]

Good morning,

i've got a table like:

TABLE1:

CODE,

VALUE,

TYPE;

in this table i have a code (90000) that's zero.

I would like to give a value like:

if(code='90000' , sum({<TYPE='E'>}VALUE), sum(VALUE)) as VALUE;

How can i do it?

[Taoufiq_Zarra]

@Carlaf_93  in script ?

you want to replace all value by sum(Value) or Sum(Value of type E if code =90000) ?

[Carlaf_93]

yes, in the script area  to replace the sum of value where type is E in the code 90000

[Taoufiq_Zarra]

@Carlaf_93  Maye  be if you want to keep value for all avlue and sum() for 9000 like :

TABLE1:

LOAD * INLINE [
    CODE, VALUE, TYPE
    1, 10, A
    2, 20, E
    3, 30, E
    4, 40, A
    90000, 90, B
];
left join 
load sum(VALUE) as SUM90000 resident TABLE1 where TYPE='E';

OUTPUT:
noconcatenate

load CODE, if(CODE='90000',SUM90000,VALUE) as VALUE, TYPE resident TABLE1;

drop table TABLE1;

output:

[Taoufiq_Zarra]

or replace all by sum like :

TABLE1:

LOAD * INLINE [
    CODE, VALUE, TYPE
    1, 10, A
    2, 20, E
    3, 30, E
    4, 40, A
    90000, 90, B
];
left join 
load sum(VALUE) as SUM90000 resident TABLE1 where TYPE='E';
left join 
load sum(VALUE) as SUMVALUE resident TABLE1 ;


OUTPUT:
noconcatenate

load CODE, if(CODE='90000',SUM90000,SUMVALUE) as VALUE, TYPE resident TABLE1;

drop table TABLE1;

output:

[Carlaf_93]

My data are like:

LOAD * INLINE [

CODE, TYPE, VALUE,

1,E,10,

2,P,23,

90000,P,0];

i would like to replace the value 0 in code 900000 with the sum(value) where type='E'

[Taoufiq_Zarra]

@Carlaf_93 

TABLE1:

LOAD * INLINE [

CODE, TYPE, VALUE

1,E,10

2,P,23

90000,P,0];
left join 
load sum(VALUE) as SUM90000 resident TABLE1 where TYPE='E';

OUTPUT:
noconcatenate

load CODE, if(CODE='90000',SUM90000,VALUE) as VALUE, TYPE resident TABLE1;

drop table TABLE1;

output:

[Carlaf_93]

Thanks. I have also a year field... how shoul i insert it?

[Taoufiq_Zarra]

@Carlaf_93  just add it after comma .

TABLE1:

LOAD * INLINE [

CODE, TYPE, VALUE,OTHERFIELD

1,E,10,other

2,P,23,other

90000,P,0,other];
left join 
load sum(VALUE) as SUM90000 resident TABLE1 where TYPE='E';

OUTPUT:
noconcatenate

load CODE, if(CODE='90000',SUM90000,VALUE) as VALUE, TYPE,OTHERFIELD resident TABLE1;

drop table TABLE1;

output:

[Carlaf_93]

Something isn't correct... it gives me the double of the value of all the years...

so code 90000 of year 2020 = 2000000

code 90000 of year 2019 = 2000000

code 90000 of year 2018 = 2000000