if condition for "n" values

[cosinho89]

Hi,

below my set  of data:

localteam_id	visitorteam_id	scores_localteam_score	scores_visitorteam_score
8	26	1	0
19	15	1	3
33	13	2	2
3	11	0	1
10	65	0	1
1	69	2	0
30	14	1	4
51	6	0	1
18	22	2	0
9	20	4	0
11	19	1	3
13	10	0	0
22	33	1	0
20	1	0	0
65	3	1	1
26	51	2	1
15	8	0	1

i need to calculate the average of goal for each team.

I set this expression: If(localteam_id=8 , scores_localteam_score,0) +
If(visitorteam_id=8,scores_visitorteam_score,0). 

it works but i want to know how to calculate the value for each id automatically. is it possible? i don't want to reply the formula n times.

Thank you in advance,

M

[Taoufiq_Zarra]

Maye be :

in loadScript :

Data:

LOAD * INLINE [
    localteam_id, visitorteam_id, scores_localteam_score, scores_visitorteam_score
    8, 26, 1, 0
    19, 15, 1, 3
    33, 13, 2, 2
    3, 11, 0, 1
    10, 65, 0, 1
    1, 69, 2, 0
    30, 14, 1, 4
    51, 6, 0, 1
    18, 22, 2, 0
    9, 20, 4, 0
    11, 19, 1, 3
    13, 10, 0, 0
    22, 33, 1, 0
    20, 1, 0, 0
    65, 3, 1, 1
    26, 51, 2, 1
    15, 8, 0, 1
];

Temps:
noconcatenate

load localteam_id,scores_localteam_score resident Data;
join
load visitorteam_id as localteam_id, scores_visitorteam_score resident Data;

output:
noconcatenate

load localteam_id,if(isnull(scores_visitorteam_score),0,scores_visitorteam_score) as scores_visitorteam_score,if(isnull(scores_localteam_score),0,scores_localteam_score) as scores_localteam_score resident Temps;

drop table Data,Temps;

and in Interface :

Dimension :localteam_id,..

Expression : scores_localteam_score+scores_visitorteam_score

output :

[Lisa_P]

In the load script you can reload the data into another table like this:

Results:
Load localteam_id as Team ,
'Home' as Type,
RecNo() as Round,
scores_localteam_score as Score
Resident Table;

Load visitorteam_id as Team ,
'Visitor' as Type,
RecNo() as Round,
scores_visitorteam_score as Score
Resident Table;

Then use Team as dimension and Sum(Score) as measure for total goals, Count(Round) for games played and divide [total goals]/[games played] for average.

[cosinho89]

thank you very much it works!!!