Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody.
I need you!
I would like to calculate the average of X first time values and i don't find the solution.
Exemple :
Town Date Time
ARLON | 1/01/2013 | 07:00 |
ARLON | 1/01/2013 | 10:00 |
ARLON | 1/01/2013 | 09:00 |
ARLON | 1/01/2013 | 08:00 |
ARLON | 2/01/2013 | 10:00 |
ARLON | 2/01/2013 | 11:00 |
ARLON | 2/01/2013 | 12:00 |
ARLON | 2/01/2013 | 13:00 |
ARLON | 2/01/2013 | 14:00 |
GENT | 1/01/2013 | 06:00 |
GENT | 1/01/2013 | 07:00 |
GENT | 1/01/2013 | 08:00 |
GENT | 1/01/2013 | 05:00 |
I would like to have the average of the 3 first times(chronological) for each town and each date.
So :
ARLON 1/01/2013 08:00 because the range is (07:00, 08:00, 09:00)
ARLON 2/01/2013 11:00 because the range is (10:00, 11:00, 12:00)
GENT 1/01/2013 06:00 because the range is (05:00, 06:00, 07:00)
I hope and will appreciate your help...
Thanks
Try like below:
Temp:
LOAD * , City&'-'&Date AS Key INLINE [
City , Date , Time
ARLON, 1/01/2013, 07:00
ARLON, 1/01/2013, 10:00
ARLON, 1/01/2013, 09:00
ARLON, 1/01/2013, 08:00
ARLON, 2/01/2013, 10:00
ARLON, 2/01/2013, 11:00
ARLON, 2/01/2013, 12:00
ARLON, 2/01/2013, 13:00
ARLON, 2/01/2013, 14:00
GENT, 1/01/2013, 06:00
GENT, 1/01/2013, 07:00
GENT, 1/01/2013, 08:00
GENT, 1/01/2013, 05:00
];
Temp_1:
LOAD * ,
IF(Key<>Previous(Key) , 1 , 1+Peek(FLAG)) AS FLAG
Resident Temp Order By City , Date , Time;
DROP Table Temp ;
Final:
LOAD City , Date , Avg(Time) AS Time
Resident Temp_1
Where FLAG < 4
Group By City , Date ;
DROP Table Temp_1 ;
Please find the attached file for reference.
Hi
just an idea on which to experiment, see attachment
Regards
Try like below:
Temp:
LOAD * , City&'-'&Date AS Key INLINE [
City , Date , Time
ARLON, 1/01/2013, 07:00
ARLON, 1/01/2013, 10:00
ARLON, 1/01/2013, 09:00
ARLON, 1/01/2013, 08:00
ARLON, 2/01/2013, 10:00
ARLON, 2/01/2013, 11:00
ARLON, 2/01/2013, 12:00
ARLON, 2/01/2013, 13:00
ARLON, 2/01/2013, 14:00
GENT, 1/01/2013, 06:00
GENT, 1/01/2013, 07:00
GENT, 1/01/2013, 08:00
GENT, 1/01/2013, 05:00
];
Temp_1:
LOAD * ,
IF(Key<>Previous(Key) , 1 , 1+Peek(FLAG)) AS FLAG
Resident Temp Order By City , Date , Time;
DROP Table Temp ;
Final:
LOAD City , Date , Avg(Time) AS Time
Resident Temp_1
Where FLAG < 4
Group By City , Date ;
DROP Table Temp_1 ;
Please find the attached file for reference.
Hi Dathu.qv
Hi Massimo Grossi
Thanks for your solution. That's fine.
Regards