Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikviewalex
New 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

Tags (4)
1 Solution

Accepted Solutions
adamdavi3s
Honored Contributor

Re: Load only min date and time with key

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;

11 Replies
adamdavi3s
Honored Contributor

Re: Load only min date and time with key

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
Honored Contributor

Re: Load only min date and time with key

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

adamdavi3s
Honored Contributor

Re: Load only min date and time with key

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

Re: Load only min date and time with key

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

MVP
MVP

Re: Load only min date and time with key

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
Honored Contributor

Re: Load only min date and time with key

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

qlikviewalex
New Contributor III

Re: Load only min date and time with key

Thank you for your support

qlikviewalex
New Contributor III

Re: Load only min date and time with key

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

adamdavi3s
Honored Contributor

Re: Load only min date and time with key

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