Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewalex
Contributor III
Contributor III

Load only min date and time with key

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:

   

KeyDateTimeText
Key202.01.201709:17:14b
Key104.01.201710:01:52d
Key101.01.201700:13:00a
Key107.01.201700:57:38g
Key306.01.201701:42:17f
Key208.01.201702:26:55h
Key309.01.201703:11:34i
Key303.01.201703:56:12c
Key205.01.201704:40:50e

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

I Order the Table.

...order by Key, Date, Time;

   

KeyDateTimeText
Key101.01.201700:13:00a
Key104.01.201700:57:38d
Key107.01.201701:42:17g
Key202.01.201709:17:14b
Key205.01.201710:01:52e
Key208.01.201710:46:31h
Key303.01.201711:31:09c
Key306.01.201712:15:48f
Key309.01.201713:00:26i

Now I need only the first row per key.

   

KeyDateTimeText
Key101.01.201700:13:00a
Key202.01.201709:17:14b
Key303.01.201710:01:52c

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

I hope someone can help me.

Alex

1 Solution

Accepted Solutions
adamdavi3s
Master
Master

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;

View solution in original post

11 Replies
adamdavi3s
Master
Master

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
Master
Master

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

adamdavi3s
Master
Master

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;

Not applicable

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
MVP
MVP

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
Master
Master

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

qlikviewalex
Contributor III
Contributor III
Author

Thank you for your support

qlikviewalex
Contributor III
Contributor III
Author

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

adamdavi3s
Master
Master

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