
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
show final value of each day in the table?
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;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
;
If a post helps to resolve your issue, please accept it as a Solution.
