topic Assigning values in QlikView

Assigning values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

6/10/2016 10:54:22 am

16/10/2016 10:56:21 am

16/10/2016 10:59:10 am

16/10/2016 11:02:12 am

16/10/2016 11:10:23 am

Please help me in doing this.

Re: Assigning values

tamilarasu — Thu, 17 Nov 2016 06:27:41 GMT

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:

Re: Assigning values

Anonymous — Thu, 17 Nov 2016 09:12:06 GMT

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?

Re: Assigning values

tamilarasu — Thu, 17 Nov 2016 09:31:35 GMT

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.

Re: Assigning values

Chanty4u — Thu, 17 Nov 2016 10:17:33 GMT

you are great bro