Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
varmekontrol
Creator
Creator

Load latest timestamp, from dates.

Hi

I think I have read every community thread I could find about this, and no solution I found solves my problem. 

I need to extract the latest timestamp from every date in my table. 

LOAD * INLINE [
    ID, TimeStamp, Value
555,	31-10-2019 23:15,	500
555,	31-10-2019 23:30,	100
555,	31-10-2019 23:46,	100 //I need this value
555,	01-11-2019 00:01,	100
555,	01-11-2019 00:17,	100
555,	01-11-2019 00:32,	100
555,	01-11-2019 00:48,	100
555,	01-11-2019 01:03,	100
555,	01-11-2019 01:19,	100
555,	01-11-2019 01:34,	100
555,	01-11-2019 01:50,	100
555,	01-11-2019 02:05,	100
555,	01-11-2019 02:21,	100
555,	01-11-2019 02:36,	100 //I need this value

];

 

I need a way to only load the Max(Timestamp) from each date.

1 Solution

Accepted Solutions
cengizeralp
Contributor III
Contributor III

I think, grouping clause is missing at the second load statement

"Group by SensorID, date"

View solution in original post

7 Replies
Anil_Babu_Samineni

In the script/design?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
varmekontrol
Creator
Creator
Author

Sorry. 

I want to do it in the Load script. 

petter
Partner - Champion III
Partner - Champion III

SET TimestampFormat='DD-MM-YYYY hh:mm';
SET DateFormat='DD-MM-YYYY';

LOAD
  Date,
  TimeStamp(Max(TimeStamp)) AS LatestTimeStamp,
  FirstSortedValue(Value,-TimeStamp) AS Value
GROUP BY
  Date;
LOAD
  *,
  Date(Floor(TimeStamp)) AS Date
INLINE [

  ID, TimeStamp, Value
  555, 31-10-2019 23:15, 500
  555, 31-10-2019 23:30, 100
  555, 31-10-2019 23:46, 100, //I need this value
  555, 01-11-2019 00:01, 100
  555, 01-11-2019 00:17, 100
  555, 01-11-2019 00:32, 100
  555, 01-11-2019 00:48, 100
  555, 01-11-2019 01:03, 100
  555, 01-11-2019 01:19, 100
  555, 01-11-2019 01:34, 100
  555, 01-11-2019 01:50, 100
  555, 01-11-2019 02:05, 100
  555, 01-11-2019 02:21, 100
  555, 01-11-2019 02:36, 100, //I need this value
];

 

or like this:

 
LOAD
  Date,
  TimeStamp(Max(TimeStamp)) AS LatestTimeStamp,
  FirstSortedValue(Value,-TimeStamp) AS Value
GROUP BY
  Date;
LOAD
  *,
  Date(Floor(TimeStamp)) AS Date
FROM
  SourceFile.txt (txt);

 

marcus_sommer

You could try it with this:

t: load *, date(floor(Timestamp)) as D, time(frac(Timestamp)) as t from Source;
    inner join(t) load ID, D, max(t) as t resident t;

- Marcus

varmekontrol
Creator
Creator
Author

I am getting an error when trying to use this solution: 

 

Temp:
LOAD
    @2 as SensorID,
	date(floor(@3)) as date,
    time(frac(@3)) as time,
    @10  as value
    
FROM [lib://Co2/Co2Sensor_201911.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines) where IsNum(@2) and @2<>23000290 and @11<101 and @3>=today()-200;;

Temp2:
inner join(Temp)
load 
	SensorID, 
	date, 
    max(time) as time 
resident Temp;

Drop Table Temp;
cengizeralp
Contributor III
Contributor III

I think, grouping clause is missing at the second load statement

"Group by SensorID, date"

varmekontrol
Creator
Creator
Author

This was the final solution with the help from @marcus_sommer and @cengizeralp (thx guys)

Temp:
LOAD
    @2 as SensorID,
	date(floor(@3)) as date,
    time(frac(@3)) as time,
    @10  as value
    
FROM [lib://Co2/Co2Sensor_201911.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines) where IsNum(@2) and @2<>23000290 and @11<101 and @3>=today()-200;;

inner join(Temp)
load 
	SensorID, 
	date, 
    max(time) as time 
resident Temp group by SensorID, date;