Find the current and previous designation of a Employee in q/kdb+

Find the current and previous designation employees by comparing current date with previous updated

flip dc:`eid`name`titleID`title`lastupdate!(("1234" ;"1234"; "1234" ;"9835" ;"9835"; "9835"; "0982");("Jhon";"Jhon";"Jhon";"Chris";"Chris";"Chris";"jack");("09561";"08123";"08123" ;"07812"; "05234"; "05234"; "08123");("SalesManager";"Salesexecutive";"Salesexecutive";"SeniorSalesExecutive";"Salesexecutive";"Salesexecutive";"salesmanager");2023.06.26 2023.06.25 2023.06.20 2023.06.26 2023.06.25 2022.06.20 2022.01.19)

EmployeeTAB

Query select from EmployeeTAB where lastupdate<td,lastupdate=(max;lastupdate) fby eID; results the latest updated result

output

Remove employees with only 1 role using fby.

Sort remaining by id and latest date and group the table on eid.

Then just take the first 2 entries and ungroup

`eid xasc ungroup 2#''`eid xgroup`eid`lastupdate xdesc select from employeeTAB where 1<(count;i)fby eid

One way to achieve desired result is to

1. Get reference table of eids and two most recent lastupdates
2. Join reference table with other data by eid and lastupdate

Like below

(ungroup select -2#asc lastupdate by eid from EmployeeTAB) 
  ij `eid`lastupdate xkey EmployeeTAB

Deleting employees, who only had one role makes query more complicated:

delete from distinct (
  (ungroup select -2#asc lastupdate by eid from EmployeeTAB) 
    ij `eid`lastupdate xkey EmployeeTAB
) where 1=(count;eid) fby eid

Another approach (assumes data is sorted by lastupdate as per the sample):

q)select from EmployeeTAB where({(1<count x)&x&not 2<sums x};lastupdate<=.z.D)fby eid
eid    name    titleID title                  lastupdate
--------------------------------------------------------
"1234" "Jhon"  "09561" "SalesManager"         2023.06.26
"1234" "Jhon"  "08123" "Salesexecutive"       2023.06.25
"9835" "Chris" "07812" "SeniorSalesExecutive" 2023.06.26
"9835" "Chris" "05234" "Salesexecutive"       2023.06.25

q)select from EmployeeTAB where({(1<count x)&x&not 2<sums x};lastupdate<=2023.06.25)fby eid
eid    name    titleID title            lastupdate
--------------------------------------------------
"1234" "Jhon"  "08123" "Salesexecutive" 2023.06.25
"1234" "Jhon"  "08123" "Salesexecutive" 2023.06.20
"9835" "Chris" "05234" "Salesexecutive" 2023.06.25
"9835" "Chris" "05234" "Salesexecutive" 2022.06.20
q)

res:(select from employeeTab where lastupdate<=tradingDate,lastupdate=(max;lastupdate) fby eid) lj 1! select eid,prevTitleID:titleID,prevTitle:title,preLastupdate:lastupdate from employeeTAB where lastupdate<=tradingDate,lastupdate=(max;lastupdate) fby eid

select from res where titleID<>prevTitle, not prevtitle like ""