First Pending Orders Snapshot by Day and Time

[wanass123]

I have a table show the pending orders multiple times in a day 

like this : 

DateTIme	Total _Pending
3/11/24 11:00 AM	22
3/11/24 10:00 AM	44
3/11/24 1:00 PM	12
3/12/24 11:00 AM	43
3/12/24 7:00 AM	45
3/12/24 9:00 AM	66

I need the Total pending for the first record by day  & time , result : 

DateTIme	Total _Pending
3/11/24 10:00 AM	44
3/12/24 7:00 AM	45

[rubenmarin · Accepted solution]

Hi, you can do an inner join to reduce data to the first alue of each date using and script like:

DataTable:
LOAD *, Date(Floor(DateTIme)) as Date;
LOAD Timestamp(Timestamp#(DateTIme,'M/D/YY hh:mm tt')) as DateTIme, Total_Pending
INLINE [
    DateTIme, Total_Pending
    3/11/24 11:00 AM, 22
    3/11/24 10:00 AM, 44
    3/11/24 1:00 PM, 12
    3/12/24 11:00 AM, 43
    3/12/24 7:00 AM, 45
    3/12/24 9:00 AM, 66
];

Inner Join
LOAD
	Date,
	Timestamp(Min(DateTIme)) as DateTIme
Resident DataTable
Group By Date;

Or doing a sorted load from the loaded table and use exists to only keep the first record:

FinalData:
LOAD
	Date as LoadedDate,DateTIme,Total_Pending
Resident DataTable
Where not Exists('LoadedDate',Date)
Order By DateTIme;

DROP Table DataTable;

[wanass123]

For some reason, the code didn't work, but I used the same idea to create a table with the minimum date and time. Then, I used an inner join to retrieve the first date and time data from each date, and it worked fine. Thank you so much for your time and effort.