sum(field) only if another field is "distinct"

[Anonymous]

Hello Community,

I'm in trouble with a graph: I'd like to create a graph in which I need an expression that sum a field but only when another field or fields in the same table present distinct values. I mean, suppose to have the following table:

ID          CHARACTER       MONEY-PAID      TIMES

P1              pippo                   3000                   1

1                pippo                   3000                   1

P2             minnie                  1000                   5

P3             pluto                     200                     6

3                pluto                    200                     6

3                pluto                    500                     1

In this case I'd like to sum(TIMES) but I understand that the first and the second record represent the same event (pippo has 2 ID and unfortunately I can't change that), then also the 4th and 5th event are the same, while the last one is not connected to the previous 2! What kind of expression do I have to use to find the right value 1+5+6+1 for my sum of TIMES?

Thanks to everyone's going to answer me!

Bye Bye

[#2]

Hey check the AGGR() function in the reference manual.

Cheers

Juan Pedro

[maxgro · Accepted solution]

Hi Irene

try to add an expression

aggr(avg(TIMES), CHARACTER, [MONEY-PAID] )

[danieloberbilli]

What is the exact rule to identify the right ID - as I understand your example its not always the 'P'X ID?

If you say 1+5+6+1  that means regarding the IDs  P1+P2+P3+3 ?

I would suggest that you first clean the ID: e.g. like this:

Data_tmp:

load

*,

right(ID,1) as ID_nr;

LOAD * INLINE [

    ID, CHARACTER, MONEY-PAID, TIMES

    P1, pippo, 3000, 1

    1, pippo, 3000, 1

    P2, minnie, 1000, 5

    P3, pluto, 200, 6

    3, pluto, 200, 6

    3, pluto, 500, 1

];

Data:

NoConcatenate load * Resident Data_tmp Order by ID_nr;

DROP Table Data_tmp;

[Anonymous]

Hi Daniel, I admit I've semplify my problem cos' I've got a huge source where the characters are doctors (Mario Rossi), the IDs are fixed by the Hospital (MarRos or 1123 stand both for Mario Rossi) and the other fields are Money and Number of DayHospital respectively. So I gave up working on the IDs.

Whatever thank you.

[Anonymous]

Hi Massimo, I think that's the right way for me to follow... I'm going to test it... Thank you so much!!!

[danieloberbilli]

Then maybe the solution of Massimo should work for you. Generally I would recommend to create a unique ID - there are several ways to do so. If you post an Excel-list with representative IDs (so that we see how different the IDs might be) than its easy to give you a solution how to create clean and unique IDs

[danieloberbilli]

Amendment: I also like Massimos solution - but be aware that the field CHARACTER is used to identify the cases. If the CHARACTER Mario Rossi is once written as M. Rossi, Rossi or Mario R. - you will not get it right!

[maxgro]

Irene

I think at the script level this can also be used to identify (Flag) the first record based on two fields (CHARACTER, MONEY-PAID) that can change content

Tmp:

LOAD * INLINE [

ID,          CHARACTER ,      MONEY-PAID  ,    TIMES

P1,              pippo  ,                 3000,                   1

1 ,               pippo  ,                 3000,                   1

P2,             minnie    ,              1000   ,                5

P3,             pluto      ,               200   ,                  6

3 ,               pluto     ,               200   ,                  6

3 ,               pluto      ,              500    ,                 1

];

Table:

NoConcatenate load

ID,

CHARACTER,

[MONEY-PAID],

TIMES,

if(peek([MONEY-PAID])<>[MONEY-PAID] or peek(CHARACTER)<>CHARACTER, 1, 0) as Flag

resident Tmp

order by CHARACTER, [MONEY-PAID];

DROP Table Tmp;