Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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.