Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Load only the latest time based on a date

Hi

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.

      

IDTimeStampStartdateDescriptionTitelTreater
124587112017-02-23 12:022017-03-31ussTfAmr Tam
124587112017-02-13 12:412017-03-31ussTfAmr Tam
124587112017-02-23 09:262017-03-31ussTfAmr Tam
Tags (1)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Load only the latest time based on a date

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;

View solution in original post

5 Replies

Re: Load only the latest time based on a date

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nehasaxena
Contributor II

Re: Load only the latest time based on a date

This thread might help you -

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

Regards,

Neha

antoniotiman
Honored Contributor III

Re: Load only the latest time based on a date

Hi Theodor,

You need :

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

antoniotiman
Honored Contributor III

Re: Load only the latest time based on a date

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;

View solution in original post

Not applicable

Re: Load only the latest time based on a date

Thx worked as a charm!