Last Known Change Record

zswanson1

Forums

Hey Brian,
I am not sure if it would make more sense to create this logic by custom function or script. Essentially I have a data set that contains records regarding changes made by users to reservations.
What I would like to do is to tag (custom function?) or extract (script?) all "last known changes" for a particular record number (FMR_FOCUSREC). The last change registered  for a record number would ofcourse be determined by the FMR_CHG_DT and FMR_CHG_TIME being the most recent. The table below is a modified example data set in which I added the LKN column representing the last known change tag. Any insight you can provide would be great. Hope all is well. 
Example data set:

FMR_FOCUSREC
FMR_STATUS
FMR_ACTION
FMR_CHG_BY
FMR_CHG_DT
FMR_CHG_TIME
LKN

WORLIJ160002
Checked-Out
Reservation Creation Changed
BOOKINGENGINE
5/1/2016
0:00:00
 

WORLIJ160002
Checked-Out
Postal Address Changed
xxxxxx
7/28/2016
11:08:45
 

WORLIJ160002
Checked-Out
Amenity Change
xxxxxx
7/29/2016
17:24:15
 

WORLIJ160002
Checked-Out
Room Assignment
xxxxxx
7/29/2016
17:24:14
 

WORLIJ160002
Checked-Out
Postal Address Changed
xxxxxx
7/29/2016
17:10:51
 

WORLIJ160002
Checked-Out
Building Changed
xxxxxx
7/29/2016
17:24:14
 

WORLIJ160002
Checked-Out
Reservation Status Changed
xxxxxx
7/29/2016
17:24:22
 

WORLIJ160002
Checked-Out
Reservation Status Changed
xxxxxx
7/31/2016
14:32:16
 

WORLIJ160002
Checked-Out
License Plate Changed
xxxxxx
7/29/2016
17:10:51
Y

WATERR160003
Cancelled
Reservation Creation Changed
BOOKINGENGINE
5/20/2016
0:00:00
 

WATERR160003
Cancelled
Reservation Status Changed
xxxxxx
7/11/2016
4:38:44
Y

VALENI160001
Cancelled
Reservation Creation Changed
BOOKINGENGINE
3/3/2016
0:00:00
 

VALENI160001
Cancelled
Reservation Status Changed
xxxxxx
6/8/2016
5:35:26
Y

 

zswanson1 Thu, 09/01/2016 - 17:28

I apollogize but it looks like the example table I submitted did not post the way I created it (visually). I went ahead and uploaded a screen shot instead. 

Brian Element Thu, 09/01/2016 - 19:02

Hi Zack, I have an alternative without using custom functions or a script.

First take your file and sort it on FMR_FOCUS_REC - ascending or descending it doesn't mater, FMR_CHG_DT descending and FMR_CHG_DT_TIME descending.  Now your sorted database the last record will be your first record for each FMR_FOCUSREC.

You then take either file and summarize it by FMR_FOCUSREC, you just want a file with one of each item.

You then perform a join with the summary file being the primary file and the sorted file being secondary.  The FMR_FOCUSREC is the key for both.  So the new file will contain all the last transactions for each key.

Let me know if that makes sense.

Brian

zswanson1 Fri, 09/02/2016 - 13:08

I tested the db with your logic and it worked! Thanks Brian! I really appreciate the insight and quick response. So essentially the summarization works the same logic as excel would when you sort your records as desired prior to removal of duplicates, then excel keeps/pulls the very first record as it scans from top to bottom. 

Brian Element Fri, 09/02/2016 - 16:49

Hi Zack,

Glad it worked.  The trick is sorting the information first and then doing the join.  The sort puts the records you want on the top and the join then removes the first ones.

Brian