Assigning values

[Anonymous]

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:56:21 am	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.

[tamilarasu]

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:

[Anonymous]

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?

[tamilarasu · Accepted solution]

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.

[Chanty4u]

you are great bro