How to get the latest status from date per ID

[Saif1]

Hello Qlikers, 

My case is that i have multiple employees that has some recruitment status :

EMP num, Emp Name , Emp Status      Date

1                    A                       Sent request   1/9/2022

1                     A                      Attend Exam    5/9/2022

1                     A                      Interview    10/9/2022

1                     A                      Accepted   30/9/2022

2                     B                    Sent request   10/10/2022

2                    B                      Did not attend exam   21/10/2022

as we can see Employee (A) has the latest status is (Accepted) so it is easy to count accepted or rejected " if the status is there) 
on the other hand, Employee (B) Latest status is (Did not attend exam) but doesn't have the (Rejected) status and therefore he won't be included in the count() function.

so my questions is , how to retrieve the latest status for every employee? 

so when i retrieve the status i do my costume calculations on it (EX : if(latest_status = not attend exam, rejected))

[RsQK · Accepted solution]

Hi, this should do the trick:

temp:
LOAD * INLINE [
EMP num,Emp Name,Emp Status,Date
1,A,Sent request,01.09.2022
1,A,Attend Exam,05.09.2022
1,A,Interview,10.09.2022
1,A,Accepted,30.09.2022
2,B,Sent request,10.10.2022
2,B,Did not attend exam,21.10.2022
];

//Custom rules:
[ml_status_adj]:
MAPPING LOAD * INLINE [
From|To
Did not attend exam|Rejected
] (DELIMITER IS '|');

//Latest status:
temp_latest_status:
LOAD DISTINCT
"EMP num",
"Emp Status",
Date
RESIDENT temp;

LEFT JOIN (temp_latest_status)
LOAD
FIELDVALUE('Emp Status',RECNO()) AS "Emp Status",
APPLYMAP('ml_status_adj',FIELDVALUE('Emp Status',RECNO()))	AS "Emp Status Adjusted"
AUTOGENERATE FIELDVALUECOUNT('Emp Status');

INNER JOIN (temp_latest_status)
LOAD
"EMP num",
MAX(Date) AS Date
RESIDENT temp_latest_status
GROUP BY "EMP num";

LEFT JOIN (temp)
LOAD
"EMP num",
"Emp Status Adjusted"	AS "Latest Status"
RESIDENT temp_latest_status;

DROP TABLE temp_latest_status;

and in the frontend:

[Saif1]

thank you so much !!

[agigliotti]

Hi @Saif1 ,

In UI you can use the below expression:
if( FirstSortedValue( [Emp Status], -Date) = 'Did not attend exam', 'Rejected', FirstSortedValue( [Emp Status], -Date) )

Best Regards