topic Re: Setting a Flag in App Development

Setting a Flag

Ironzem — Mon, 12 Sep 2022 09:44:39 GMT

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

Re: Setting a Flag

rubenmarin — Mon, 12 Sep 2022 11:44:04 GMT

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;

Re: Setting a Flag

Ironzem — Mon, 12 Sep 2022 16:57:05 GMT

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

Re: Setting a Flag

rubenmarin — Wed, 14 Sep 2022 06:39:53 GMT

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

Re: Setting a Flag

Ironzem — Tue, 20 Sep 2022 04:10:44 GMT

Thanks Rubenmarin

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