topic Re: Latest Record Based on Timestamp in QlikView

Latest Record Based on Timestamp

sunitha_chellaiah — Wed, 11 Jul 2018 07:06:40 GMT

Hi,

I need to fetch the latest record from table based on Updated Date and Updated Time field

Note: Needs to do it back end script

Shift	Values	Sr. No	Updated Date	Updated Time
1	0.43	1000	22-10-2017	15:44:08
1	0.36	2000	22-10-2017	21:10:39
1	0.48	3000	22-10-2017	21:10:38
2	0.43	4000	22-10-2017	21:13:05
3	0.43	5000	22-10-2017	21:12:16
1	0.12	6000	23-11-2017	21:12:15
2	0.12	7000	23-11-2017	21:12:14
2	0.13	8000	23-11-2017	15:05:38
3	0.12	9000	23-11-2017	15:05:37

Only latest time should appear against each shift.

output should come as:

Shift	Values	Sr. No	Updated Date	Updated Time
1	0.36	2000	22-10-2017	21:10:39
2	0.43	4000	22-10-2017	21:13:05
3	0.43	5000	22-10-2017	21:12:16
1	0.12	6000	23-11-2017	21:12:15
2	0.12	7000	23-11-2017	21:12:14
3	0.12	9000	23-11-2017	15:05:37

sunny_talwar — Wed, 11 Jul 2018 16:21:00 GMT

Try this

Table:

LOAD * INLINE [

Shift, Values, Sr. No, Updated Date, Updated Time

1, 0.43, 1000, 22-10-2017, 15:44:08

1, 0.36, 2000, 22-10-2017, 21:10:39

1, 0.48, 3000, 22-10-2017, 21:10:38

2, 0.43, 4000, 22-10-2017, 21:13:05

3, 0.43, 5000, 22-10-2017, 21:12:16

1, 0.12, 6000, 23-11-2017, 21:12:15

2, 0.12, 7000, 23-11-2017, 21:12:14

2, 0.13, 8000, 23-11-2017, 15:05:38

3, 0.12, 9000, 23-11-2017, 15:05:37

];

Right Join (Table)

LOAD Shift,

[Updated Date],

Max([Updated Time]) as [Updated Time]

Resident Table

Group By Shift, [Updated Date];

sunitha_chellaiah — Fri, 13 Jul 2018 07:50:59 GMT

Thank you so much! Sunny