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

[adamdavi3s]

This would be possible with an exists load, give me 5mins and will share an example but basically something like

noconcatenate

Tasks2:

LOAD hash128(Key,Date) as keyfield, Key,Date,Time,Text

Resident Tasks

Where not exists (keyfield)

order by Key, Date, Time;

[adamdavi3s]

Hmm no doesn't quite work... let me play

[adamdavi3s · Accepted solution]

This should do it:

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


];

noconcatenate

Tasks2:
LOAD Key,min(datetime) as datetime
Resident Tasks
group by Key ;

left join (Tasks2)


LOAD Key,Date,Time,Text,datetime
resident Tasks;


drop table Tasks;


drop field datetime;

[#5]

Hi Alexander,

Below there is solution. Maybe not the smartest one but it works:)

temp_Table:

LOAD Key,

     Timestamp(Makedate(Right(Date,4),SubField(Date,'.',2),Left(Date,2) )+Time(Time)) as Timestamp,

     Date,

     Time,

     Text

FROM

(ooxml, embedded labels, table is Sheet1);

Table:

LOAD Key,

Min(Timestamp) as Timestamp

Resident temp_Table

Group by Key;

Drop Table temp_Table;

Of course you need to join the "Text" field if you also want to use it. But unfortunately you cannot use it with aggregation function (Min) in second load.

BR,

Milosz

[tresesco]

Or like:

Table:

Load *, Date+Time  as TimeStamp 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 

];

NoConcatenate

Final:

Load

  Key as Key2,Date,Time(Time) as Time,Text

  Resident Table where not exists(Key2,Key) Order By Key, TimeStamp;

Drop Table Table;

[adamdavi3s]

urgh, I knew there had to be a simpler way than mine

[qlikviewalex]

Thank you for your support

[qlikviewalex]

I have implemented Adams solution because he was faster. But your way is simpler.

[adamdavi3s]

Please do feel free to mark mine as helpful and tresesco‌'s answer as correct as his is better for sure