Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I've got a table like this:
Date | Initial_time | Final_time | Index |
16/05/2014 | 18:49:00 | 19:11:00 | 30468 |
16/05/2014 | 19:11:00 | 23:59:00 | 32509 |
16/05/2014 | 0:00:00 | 1:40:00 | 32655 |
I want a table that shows the following values:
- Initial time: The value wiht the lower Index
- Final time: The value with the highes index
The result should be the following table:
Date | Initial Time | Final Time |
---|---|---|
16/05/2014 | 18:49:00 | 1:40:00 |
Thank you for your help.
Best regards,
Aleix.
Use Date and Index as dimension
in the expression write
Min(Initial_time)
Max(Final_time)
Let me know
Create a straight table with dimension Date and two expressions:
=FirstSortedValue( Initial_time, Index)
=FirstSortedValue( Final_time, -Index)
PFA
may be like attached.
Regards
ASHFAQ
In the load script load a table like
TimeTable:
LOAD Date#(Dat,'DD/MM/YYYY') as Dat, Time#(Initial_time,'hh:mm:ss') as Initial_time,Time#(Final_time,'hh:mm:ss') as Final_time, Index;
LOAD * Inline
[
Date, Initial_time, Final_time, Index
16/05/2014, 18:49:00, 19:11:00, 30468
16/05/2014, 19:11:00, 23:59:00, 32509
16/05/2014, 0:00:00, 1:40:00, 32655
];
Final:
LOAD
Date,
FirstSortedValue( Initial_time, Index) as Min_Initial_time,
FirstSortedValue( Final_time, -Index) as Max_Final_time
Resident TimeTable
Group By Date;
And take table box with
Dimension:- Date, Min_Initial_time, Max_Final_time
Or in the Expression level you can do like
Dim:- Date
Expression:- FirstSortedValue( Initial_time, Index)
Expression:- FirstSortedValue( Final_time, -Index)