Loading Biggest Hour of the day

[mariozubieta]

Hello,

I was wondering if anyones knows a way I could only load one record per day, depeding on the oldest time of that day.

For example.

I'm loading numbers history. Numbers are saved different times each day, up to 6 times a day.

The datetime field is used to save numbers. (1/1/2017 17:00:00).

Heres is an example of how it looks like:

Number	datetime
345	1/1/2017 11:00:00
234	1/1/2017 14:00:00
2345	2/1/2017 09:00:00
123	2/1/2017 12:00:00
256	2/1/2017 17:00:00
678	3/1/2017 08:00:00
342	3/1/2017 10:00:00
346	3/1/2017 13:00:00
1246	3/1/2017 16:00:00
15643	3/1/2017 19:00:00

Is there a way to only load the number for each day, with the biggest time?

thanks,

[swuehl]

Set TimestampFormat = 'D/M/YYYY hh:mm:ss';

LOAD Maxstring(datetime) as MaxDateTime,

          FirstSortedValue(Number, -datetime) as Number

FROM ...

GROUP BY DayName(datetime);

[kaushiknsolanki]

Try this,

Data:

Load Number,timestamp(timestamp#(datetime,'DD/MM/YYYY hh:mm:ss')) as DateTime inline [

Number, datetime

123, 2/1/2017 12:00:00

234, 1/1/2017 14:00:00

256, 2/1/2017 17:00:00

342, 3/1/2017 10:00:00

345, 1/1/2017 11:00:00

346, 3/1/2017 13:00:00

678, 3/1/2017 08:00:00

1246, 3/1/2017 16:00:00

2345, 2/1/2017 09:00:00

15643, 3/1/2017 19:00:00

];

MaxDate:

Load Date(Floor(DateTime)) as Date,Max(DateTime) as MaxDateTime

Resident Data group by Date(floor(DateTime));

FinalData:

NoConcatenate Load * Resident Data where Exists(MaxDateTime,DateTime);

Drop table Data,MaxDate;

Regards,

Kaushik Solanki

[effinty2112]

Hi Mario,

There are a number of ways to do this, here's another. After loading your data into a table called Table 1 then

Inner Join([Table 1])

LOAD

Max(datetime) as datetime

Resident [Table 1];

will do the trick.

Cheers

Andrew