Creating a flag

[drohm002]

Hello, how can i create a flag in my script that flags the last timestamp for a particular day, which would be the yellow row below.  Thank you!

[Kushal_Chawda]

@drohm002  try below

Data:
LOAD [Account Number],
     [LB Exam Start Date],
     [LB Exam Start Time]
FROM table;

left join(Data)
LOAD [Account Number],
     [LB Exam Start Date],
     timestamp(max([LB Exam Start Time])) as [LB Exam Start Time],
     1 as Flag
resident Data
group by [Account Number],
         [LB Exam Start Date];

[drohm002]

Hi and thanks for the response, but I am getting a '1' in all the rows, as opposed to just the yellow row below which is the last time stamp on a particular day.  Any ideas why?  I had used your code you sent me

[Kushal_Chawda]

@drohm002  Make sure that Field name are same 

Data:
LOAD [Account Number],
[LB Exam Start Date],
[LB Exam Start Time]
FROM table;

left join(Data)
LOAD [Account Number],
[LB Exam Start Date],
timestamp(max([LB Exam Start Time])) as [LB Exam Start Time],
1 as Flag
resident Data
group by [Account Number],
[LB Exam Start Date];

[MarcoWedel · Accepted solution]

maybe like this?:

table1:
LOAD * 
Inline [
AccountNumber, LBExamStartDate, LBExamStartTime
111111111, 2024-09-18, 2024-09-18 00:01:23.000000
111111112, 2024-09-18, 2024-09-18 00:02:23.000000
111111113, 2024-09-18, 2024-09-18 00:03:23.000000
111111114, 2024-09-18, 2024-09-18 00:04:23.000000
111111115, 2024-09-19, 2024-09-19 00:05:23.000000
111111116, 2024-09-19, 2024-09-19 00:06:23.000000
111111117, 2024-09-19, 2024-09-19 00:07:23.000000
111111118, 2024-09-19, 2024-09-19 00:08:23.000000
111111119, 2024-09-20, 2024-09-20 00:09:23.000000
111111120, 2024-09-20, 2024-09-20 00:10:23.000000
111111121, 2024-09-20, 2024-09-20 00:11:23.000000
111111122, 2024-09-20, 2024-09-20 00:12:23.000000
];

Join
LOAD LBExamStartDate,
     Max(LBExamStartTime) as LBExamStartTime,
     1 as IsLastTimeOfDay
Resident table1
Group By LBExamStartDate;

hope this helps
Marco

[MarcoWedel]

or maybe like

table1:
LOAD *,
     -(Window(Max(LBExamStartTime),LBExamStartDate)=LBExamStartTime) as IsLastTimeOfDay
Inline [
AccountNumber, LBExamStartDate, LBExamStartTime
111111111, 2024-09-18, 2024-09-18 00:01:23.000000
111111112, 2024-09-18, 2024-09-18 00:02:23.000000
111111113, 2024-09-18, 2024-09-18 00:03:23.000000
111111114, 2024-09-18, 2024-09-18 00:04:23.000000
111111115, 2024-09-19, 2024-09-19 00:05:23.000000
111111116, 2024-09-19, 2024-09-19 00:06:23.000000
111111117, 2024-09-19, 2024-09-19 00:07:23.000000
111111118, 2024-09-19, 2024-09-19 00:08:23.000000
111111119, 2024-09-20, 2024-09-20 00:09:23.000000
111111120, 2024-09-20, 2024-09-20 00:10:23.000000
111111121, 2024-09-20, 2024-09-20 00:11:23.000000
111111122, 2024-09-20, 2024-09-20 00:12:23.000000
];

[drohm002]

Hi Marco one question for you, it is working as you mentioned and thank you for that! What if i wanted to take it one step further and flag the last timestamp per day for each Source? see picture below.  What would i need to add to my code?

[drohm002]

i figured out the extra step, just group by the source also. thanks Marco!

[MarcoWedel]

You're welcome.

Glad it worked.