Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sorry i don't know to keep any specific title for this thread..
Consider 3 columns,
Name, rows, start_time
Name | rows | time |
---|---|---|
a | 10 | 16/10/2016 10:54:22 am |
a | 20 | 16/10/2016 10:56:21 am |
b | 30 | 16/10/2016 10:59:10 am |
b | 10 | 16/10/2016 11:02:12 am |
c | 20 | 16/10/2016 11:10:23 am |
Consider a is process which ran at 16/10/2016 10:54:22 am, as it is the least minimum time in this table, it should be the starting for all the process, so the start_time for process a is 16/10/2016 10:54:22 am end_time should be same 16/10/2016 10:54:22 am.
a with 20 rows should have start_time as 16/10/2016 10:54:22 am and end_time as 16/10/2016 10:56:21 am
b with 30 rows should have start_time as 16/10/2016 10:56:21 am am and end_time as 16/10/2016 10:59:10 am
b with 10 rows should have start_time as 16/10/2016 10:59:10 am am am and end_time as 16/10/2016 11:02:12 am
c with 20 rows should have start_time as 16/10/2016 11:02:12 am am am am and end_time as 16/10/2016 11:10:23 am
O/p Table :
Name | rows | start_time | end_time | |
---|---|---|---|---|
a | 10 | 6/10/2016 10:54:22 am | 6/10/2016 10:54:22 am | |
a | 20 | 6/10/2016 10:54:22 am | 16/10/2016 10:56:21 am | |
b | 30 |
| 16/10/2016 10:59:10 am | |
b | 10 | 16/10/2016 10:59:10 am | 16/10/2016 11:02:12 am | |
c | 20 | 16/10/2016 11:02:12 am | 16/10/2016 11:10:23 am |
Please help me in doing this.
That's the reason I used order by clause. The above code works fine even If you change the order. Have a look at the attachment.
Hi Kishore,
Kindly check the below code in your original file. Let me know if any issues.
Data:
LOAD * INLINE [
Name, rows, time
a, 10, 16/10/2016 10:54:22 am
a, 20, 16/10/2016 10:56:21 am
b, 30, 16/10/2016 10:59:10 am
b, 10, 16/10/2016 11:02:12 am
c, 20, 16/10/2016 11:10:23 am
];
Final:
Load Name,
rows,
If(RowNo()=1,time, If(Rowno()=2,Peek('start_time'), Previous(time))) as start_time,
time as end_time
Resident Data order by time,Name, rows;
DROP Table Data;
Output:
thanks for your help.
What if it is not in an order. now the Name which is having minimum time should come first.
in this,
Name | rows | time |
---|---|---|
a | 10 | 16/10/2016 10:54:22 am |
b | 30 | 16/10/2016 10:59:10 am |
a | 20 | 16/10/2016 10:56:21 am |
c | 20 | 16/10/2016 11:10:23 am |
b | 10 | 16/10/2016 11:02:12 am |
O/P :
a 10 16/10/2016 10:54:22 am 16/10/2016 10:54:22 am
a 20 16/10/2016 10:54:22 am 16/10/2016 10:56:21 am
b 30 16/10/2016 10:56:21 am 16/10/2016 10:59:10 am
and so on...
How would i get now?
That's the reason I used order by clause. The above code works fine even If you change the order. Have a look at the attachment.
you are great bro