topic Re: Load only min date and time with key in QlikView

Load only min date and time with key

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

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

Re: Load only min date and time with key

adamdavi3s — Fri, 10 Mar 2017 08:19:15 GMT

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;

Re: Load only min date and time with key

adamdavi3s — Fri, 10 Mar 2017 08:23:24 GMT

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

Re: Load only min date and time with key

adamdavi3s — Fri, 10 Mar 2017 08:35:36 GMT

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;

Re: Load only min date and time with key

Fri, 10 Mar 2017 08:36:27 GMT

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

Re: Load only min date and time with key

tresB — Fri, 10 Mar 2017 08:55:01 GMT

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;

Re: Load only min date and time with key

adamdavi3s — Fri, 10 Mar 2017 08:56:22 GMT

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

Re: Load only min date and time with key

qlikviewalex — Fri, 10 Mar 2017 08:59:41 GMT

Thank you for your support

Re: Load only min date and time with key

qlikviewalex — Fri, 10 Mar 2017 09:01:59 GMT

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

Re: Load only min date and time with key

adamdavi3s — Fri, 10 Mar 2017 09:03:44 GMT

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

Re: Load only min date and time with key

Fri, 10 Mar 2017 09:46:31 GMT

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

Re: Load only min date and time with key

vinieme12 — Fri, 10 Mar 2017 09:55:41 GMT

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