Setting a Flag

[Ironzem]

Hi , I trying to set a flag for every "ID"  where first entry  is flagged 1 and there after flag next entry as 1 only if it is greater than 2hours from first flag . Sample data

Date	ID	Time	Desired Flag Output
2022-03-01	1867175215046269447	04:36	1
2022-03-01	1867175215046269447	05:07	0
2022-03-01	1867175215046269447	05:10	0
2022-03-01	1867175215046269447	05:21	0
2022-03-01	6279882763709393	07:29	1
2022-03-01	6279882763709393	07:37	0
2022-03-01	6279882763709393	07:58	0
2022-03-01	6279882763709393	08:29	0
2022-03-01	1867175215046269447	18:00	1
2022-03-01	1867175215046269447	18:32	0
2022-03-02	1867175215046269447	04:35	1
2022-03-02	1867175215046269447	05:06	0
2022-03-02	1867175215046269447	05:12	0
2022-03-02	1867175215046269447	05:23	0
2022-03-02	6279882763709393	07:24	1
2022-03-02	6279882763709393	07:28	0
2022-03-02	6279882763709393	07:58	0
2022-03-02	6279882763709393	08:29	1
2022-03-02	1867175215046269447	17:59	1
2022-03-02	1867175215046269447	18:28	0
2022-03-02	6279882763709393	18:36	1
2022-03-02	6279882763709393	19:02	0

[rubenmarin · Accepted solution]

Hi, you can do a sorte load using Peek() to check the last row values. Maybe you also need a field that uses date and time to chek the 2 hours difference between days, it would be something like:

OriginalData:
LOAD Date, ID, Time, Date+Time as DateTime
Resident/from...

PrecessedData:
LOAD ID, Date, Time,
  If(Peek(ID)<>ID
    ,DateTime // Is the first row of the ID, this datetime is flagged
    ,If(Peek(LastFlag)+(2/24)>DateTime // If there are less than 2 hours from the last flag...
      ,Peek(LastFlag) // .. The last time flagged is copied..
      ,DateTime)) as LastFlag, // .. else this is the new date
  If(Peek(ID)<>ID
    ,1 // Is the first row of the ID, this is flagged
    ,If(Peek(LastFlag)+(2/24)>DateTime
      ,0 // Last time plus 2 hours is lower that this datetime
      ,1)) as Flag, // More than 2 hours since the last flag
Resident OriginalData
ORder By ID, Datetime;

DROP Table OriginalData;

      

[Ironzem]

Hi Rubenmarin

Thank you for your time , I tried your script but I get all the rows flagged as 1 . I thought it might be the DateTime combination , so I used a Timestamp function to join with format(YY..) , but I got same results. Please see attached my script, the field names are bit different because my earlier data was shown with labels. the flag calculation is identical as you supplied. Attached also the CSV of sample data if required. Please check my script if I have done it correctly. Thank You

[rubenmarin]

Hi, I see my answer marked as correct, that means that you already solved the issue? or still all rows are flagged as 1?

[Ironzem]

Thanks Rubenmarin

Your initial answer was perfect , error on my side when implementing .