Load only min date and time with key

[qlikviewalex]

Hello,

I load a Table.

Tasks:

LOAD [Object-ID],

     Key,

     Date,

     Time,

     Text

FROM  test.xlsx (ooxml, embedded labels, table is table1);

This Table looks like this:

Key	Date	Time	Text
Key2	02.01.2017	09:17:14	b
Key1	04.01.2017	10:01:52	d
Key1	01.01.2017	00:13:00	a
Key1	07.01.2017	00:57:38	g
Key3	06.01.2017	01:42:17	f
Key2	08.01.2017	02:26:55	h
Key3	09.01.2017	03:11:34	i
Key3	03.01.2017	03:56:12	c
Key2	05.01.2017	04:40:50	e

now I need a table with only the first Timestamp (Date+Time) for a Key.

I Order the Table.

...order by Key, Date, Time;

Key	Date	Time	Text
Key1	01.01.2017	00:13:00	a
Key1	04.01.2017	00:57:38	d
Key1	07.01.2017	01:42:17	g
Key2	02.01.2017	09:17:14	b
Key2	05.01.2017	10:01:52	e
Key2	08.01.2017	10:46:31	h
Key3	03.01.2017	11:31:09	c
Key3	06.01.2017	12:15:48	f
Key3	09.01.2017	13:00:26	i

Now I need only the first row per key.

Key	Date	Time	Text
Key1	01.01.2017	00:13:00	a
Key2	02.01.2017	09:17:14	b
Key3	03.01.2017	10:01:52	c

Unfortunately, I have no idea how I can do this reshaping.

I hope someone can help me.

Alex

[#2]

Hi,

Took me a while to understand Treseco B approach

Now I figured it out. "Final" table is sorted by key and timestamp and "not exists" function acts as "distinct" there. So it takes only first occurency of Key1, Key2, Key3 etc. which are sorted by timestamp in previous step.

Nice thinking and very useful tip. I used a lot of max/min/peek functions but this is really smart. Especially when you have more fields in your load.

Thanks!

BR,

Milosz

[vinieme12]

instead of Joining we can also use Right Keep  and this would be faster than using Exist

Tasks:

load Key,timestamp(timestamp#(Date&' '&Time,'DD.MM.YYYY hh:mm:ss')) as datetime,Date,Time,Text;

load * inline [

Key, Date, Time, Text, 

Key2, 02.01.2017, 09:17:14, b

Key1, 04.01.2017, 10:01:52, d

Key1, 01.01.2017, 00:13:00, a

Key1, 07.01.2017, 00:57:38, g

Key3, 06.01.2017, 01:42:17, f

Key2, 08.01.2017, 02:26:55, h

Key3, 09.01.2017, 03:11:34, i

Key3, 03.01.2017, 03:56:12, c

Key2, 05.01.2017, 04:40:50, e

  ];

Right keep

Tasks2:

LOAD Key,min(datetime) as datetime

Resident Tasks

group by Key ;

Drop table Tasks2