topic Re: Load only the latest time based on a date in QlikView

Load only the latest time based on a date

Fri, 13 Oct 2017 10:05:23 GMT

I have a problem i cant wrap my head around, i have example data. I want to load only the first record based on the valye TimeStamp. is that possible in Qlikview?

I have a example based on a case but my data i much bigger so i cant do this manually is this possible to do in the load? I only need the record from the first row (in red) based on the first timestamp.

ID	TimeStamp	Startdate	Description	Titel	Treater
12458711	2017-02-23 12:02	2017-03-31	uss	Tf	Amr Tam
12458711	2017-02-13 12:41	2017-03-31	uss	Tf	Amr Tam
12458711	2017-02-23 09:26	2017-03-31	uss	Tf	Amr Tam

Re: Load only the latest time based on a date

Anil_Babu_Samineni — Fri, 13 Oct 2017 10:36:26 GMT

Does this works?

Source:

load * inline [

ID, TimeStamp, Startdate, Description, Titel, Treater

12458711, 2017-02-23 12:02, 2017-03-31, uss, Tf, Amr Tam

12458711, 2017-02-13 12:41, 2017-03-31, uss, Tf, Amr Tam

12458711, 2017-02-23 09:26, 2017-03-31, uss, Tf, Amr Tam

];

Left Join (Source)

LOAD

TimeStamp,

Date( Date#(TimeStamp, 'YYYY-MM-DD hh:mm'),'YYYY-MM-DD hh:mm') as New

Resident Source;

Right Keep (Source)

LOAD

Date( Max(New), 'YYYYMMDD') as New

Resident Source;

Re: Load only the latest time based on a date

nehasaxena — Fri, 13 Oct 2017 10:41:34 GMT

This thread might help you -

https://community.qlikview.com/thread/148948

Regards,

Neha

Re: Load only the latest time based on a date

antoniotiman — Fri, 13 Oct 2017 10:42:34 GMT

Hi Theodor,

You need :

the first Row,the Latest TimeStamp or the latest Time ?

Re: Load only the latest time based on a date

antoniotiman — Fri, 13 Oct 2017 10:50:02 GMT

However

SET TimestampFormat='DD-MM-YYYY hh:mm';
Temp:
LOAD * Inline [
ID, TimeStamp, Startdate, Description, Titel, Treater
12458711, 2017-02-23 12:02, 2017-03-31, uss, Tf, Amr Tam
12458711, 2017-02-13 12:41, 2017-03-31, uss, Tf, Amr Tam
12458711, 2017-02-23 09:26, 2017-03-31, uss, Tf, Amr Tam];

Inner Join (Temp) //Latest Time
LOAD ID,FirstSortedValue(TimeStamp,-Time(TimeStamp)) as TimeStamp
Resident Temp
Group By ID;

//Inner Join (Temp) //First Row
//LOAD ID,FirstValue(TimeStamp) as TimeStamp
//Resident Temp
//Group By ID;
//
//Inner Join (Temp) //Latest TimeStamp
//LOAD ID,Min(TimeStamp) as TimeStamp
//Resident Temp
//Group By ID;

Re: Load only the latest time based on a date

Fri, 13 Oct 2017 12:45:45 GMT

Thx worked as a charm!