Forum Discussion
Find Value in Same Table
I have an audit table which contains audit log data. I am trying to isolate when a particular event happens, if another event happens on the same day for the same user.
In this example, I would be looking for when Key_ID 22 happens, is it followed by a 41 on the same date by the same user. I tried using a LOOKUPVALUE function nested in an IF, but since I did not know if or where the record could occur, I could not figure out what value to input for the search value parameter. I do have 2 multiple index on the table, one for all the table records and one for parent records that shows what order the child records happened in.
What I am looking for at a minimum is to return the date the 41 event happened on or a 1 if there is no 41. In a perfect world, I would have the column returning a 0 for instances where the 41 followed the 22 on the same day by the same user and a 1 when this condition is not met.
Any help to get me moving on this would be appreciated.
Hello KayceVC
Give this a try. I had a check for the time on the 42 being after the time on the row but it doesn't read like you need that but I left it in the code and just commented it out.
Result = VAR TheDate = DATEVALUE ( auditdata[Date_Updated] ) VAR TheDateTime = auditdata[Date_Updated] VAR TheUser = auditdata[User] VAR NumRows = 0 RETURN IF ( auditdata[Key_ID] = 22, CALCULATE ( MAX ( auditdata[Date_Updated] ), FILTER ( auditdata, auditdata[User] = TheUser && auditdata[Key_ID] = 41 && DATEVALUE ( auditdata[Date_Updated] ) = TheDate --&& auditdata[Date_Updated] > TheDateTime ) ) )
5 Replies
Hello KayceVC ,
Can you add the result column to your screen shot for what you would like to see?
ParentID ChildID Key_ID User Date_Updated TableIndex RecordIndex Result 4061 2232 21 Aguy 6/27/2019 15 1 4061 2233 22 Aguy 6/27/2019 16 2 4061 2234 2 Aguy 6/27/2019 17 3 4061 2235 22 Aguy 6/27/2019 18 4 4061 2236 3 Aguy 6/27/2019 19 5 4061 2237 41 Aguy 6/27/2019 20 6 4061 2238 1 Agirl 6/27/2019 21 7 4061 2239 3 Agirl 6/27/2019 22 8 4061 2240 1 Agirl 6/27/2019 23 9 Hey jdbuchanan71
This would be what I would expect. I would like to return the date where the 41 on the same day occurred on the row item for the associated 22. This will allow me to calculate the 2 follow up values I need a boolean for was the 41 record (a time entry note) updated within the allowed time (before the end of the day) and then a calculation for how long it took for each 22 record to receive the time entry update.
ParentID ChildID Key_ID User Date_Updated TableIndex RecordIndex Result 4061 2232 21 Aguy 6/27/19 1:00 15 1 4061 2233 22 Aguy 6/27/19 2:00 16 2 6/27/19 2:45 4061 2234 2 Aguy 6/27/19 2:15 17 3 4061 2235 22 Aguy 6/27/19 2:30 18 4 6/27/19 2:45 4061 2236 3 Aguy 6/27/19 2:30 19 5 4061 2237 41 Aguy 6/27/19 2:45 20 6 4061 2238 1 Agirl 6/27/19 3:05 21 7 4061 2239 3 Agirl 6/27/19 3:15 22 8 4061 2240 1 Agirl 6/27/19 4:00 23 9 PattemManohar Unfortunately this is as much sample data as I can provide. It is formatted exactly how my real data is, but due to the senstive nature of my data, I am not permitted to post actual system data. The above sample is very close to my ideal input/output though.
Hello KayceVC
Give this a try. I had a check for the time on the 42 being after the time on the row but it doesn't read like you need that but I left it in the code and just commented it out.
Result = VAR TheDate = DATEVALUE ( auditdata[Date_Updated] ) VAR TheDateTime = auditdata[Date_Updated] VAR TheUser = auditdata[User] VAR NumRows = 0 RETURN IF ( auditdata[Key_ID] = 22, CALCULATE ( MAX ( auditdata[Date_Updated] ), FILTER ( auditdata, auditdata[User] = TheUser && auditdata[Key_ID] = 41 && DATEVALUE ( auditdata[Date_Updated] ) = TheDate --&& auditdata[Date_Updated] > TheDateTime ) ) )
KayceVC Could you please post the sample data that can be copied instead of screenshot. Also, what is your expected output... Please post the expected output as well.
Is it just to list ParentIDs and Flag (if there is a Key_ID 22 and followed by 41 for same user on same day)
