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: 
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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
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;