show final value of each day in the table?

[lucasx15]

I have the following situation. I need to get the last day and time (record) of each line and shift so that it shows me the value. For example, on 01/20/2023 at 5:00 am I need the last record for that day and time and shift to appear in my account. But I'm quite confused how to do it. What I've done so far is this:

TEMP:
LOAD SET										AS %CD_SET, 
     date(DT_DAY_OCUP,'DD/MM/YYYY') 			as DT_DAY_OCUP,
     DT_HORA_DIA_OCUPACAO, 
     Timestamp(CURRENT_DATE)					AS CURRENT_DATE,
     INTERN								AS INTERN.OCUP, 
     TOTAL										AS TOTAL.OCUP,
     time(frac(CURRENT_DATE),'hh:mm:ss') 		as CURRENT_DATE_TIME,
     timestamp(CURRENT_DATE,'DD/MM/YYYY') 		AS DT_OCUP,
     if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(7,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(12,59,59), 'M',
     	if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(13,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(18,59,59), 'T',
     		if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(19,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(23,59,59) or
     			time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(0,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(0,59,59), 'N 1',
     IF(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(1,0,0) AND time(frac(CURRENT_DATE),'hh:mm:ss') <=MakeTime(6,59,59), 'N 2')))) as TURN.OCUP
FROM $(Archive)CLIENT_OCUP_HOUR.qvd (qvd);

CLIENT_OCUP_HOUR:
LOAD *,
	 %CD_SET&'|'&DATE(DT_DAY_OCUP,'DD/MM/YYYY')&'|'&TURN.OCUP 	as %SET_DAY_TURN,
	 FirstSortedValue(CURRENT_DATE,-1) AS DT_TESTER
RESIDENT TEMP;

DROP TABLE TEMP;

[edwin]

you should be able to aggregate your data by LINE, SHIFT and max date/time.  this is assuming you have a table which has the 3 fields - line, shift date/time.  from the script above it isnt clear which ones are the shift, line, datetime.

you can then left join this to your data and add a flag for last record

this means you will introduce a new field which tells you which one is your last record.  you can then use that in your set analysis

[edwin · Accepted solution]

here is a sample demo:

data:
load * inline [
Line, Shift, DateTime
1,1,1/1/2023 5:00:00 AM
1,1,1/1/2023 7:00:00 AM
1,1,1/1/2023 8:00:00 AM
1,2,1/1/2023 9:00:00 AM
1,2,1/1/2023 5:00:00 PM
2,1,1/1/2023 5:00:00 AM
2,1,1/1/2023 10:00:00 AM
];

left join (data)
load Line, Shift, date(max(DateTime),'$(TimestampFormat)') as DateTime, 1 as IsMax
Resident data
group by Line, Shift;

resulting table:

[avinashelite]

If I understand your problem statement correctly , the users like to pick a date and time , based on time and picked you need to show the latest details in the front end ?

If yes , then you need read the selected time and date in the frontend to a variable  (vSelection), then use this variable to add the condition like  if (Date_Time_field<vSelection, max(Required_field))

If you could attached sample data that could be easy to provide the solution

[vinieme12]

few changes highlighted

CLIENT_OCUP_HOUR:
LOAD SET AS %CD_SET,
date(floor(DT_DAY_OCUP),'DD/MM/YYYY') as DT_DAY_OCUP,
DT_HORA_DIA_OCUPACAO,
Timestamp(CURRENT_DATE) AS CURRENT_DATE,
INTERN AS INTERN.OCUP,
TOTAL AS TOTAL.OCUP,
time(frac(CURRENT_DATE),'hh:mm:ss') as CURRENT_DATE_TIME,
timestamp(CURRENT_DATE,'DD/MM/YYYY') AS DT_OCUP,
if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(7,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(12,59,59), 'M',
if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(13,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(18,59,59), 'T',
if(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(19,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(23,59,59) or
time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(0,0,0) and time(frac(CURRENT_DATE),'hh:mm:ss') <= MakeTime(0,59,59), 'N 1',
IF(time(frac(CURRENT_DATE),'hh:mm:ss') >=MakeTime(1,0,0) AND time(frac(CURRENT_DATE),'hh:mm:ss') <=MakeTime(6,59,59), 'N 2')))) as TURN.OCUP
FROM $(Archive)CLIENT_OCUP_HOUR.qvd (qvd);

Left Join (CLIENT_OCUP_HOUR)
LOAD  %CD_SET, DT_DAY_OCUP , TURN.OCUP 

%CD_SET&'|'& DT_DAY_OCUP &'|'&TURN.OCUP  as %SET_DAY_TURN,
Max(CURRENT_DATE) AS DT_TESTER
RESIDENT TEMP

Group By 

 %CD_SET, DT_DAY_OCUP , TURN.OCUP 

;