Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Irene
try to add an expression
aggr(avg(TIMES), CHARACTER, [MONEY-PAID] )
Hey check the AGGR() function in the reference manual.
Cheers
Juan Pedro
Hi Irene
try to add an expression
aggr(avg(TIMES), CHARACTER, [MONEY-PAID] )
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;
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.
Hi Massimo, I think that's the right way for me to follow... I'm going to test it... Thank you so much!!!
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
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!
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;