Load latest timestamp, from dates.

[varmekontrol]

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.

[Anil_Babu_Samineni]

In the script/design?

[varmekontrol]

Sorry. 

I want to do it in the Load script. 

[petter]

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]

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 · Accepted solution]

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

"Group by SensorID, date"

[varmekontrol]

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;