topic Re: How to get the latest status from date per ID in App Development

How to get the latest status from date per ID

Saif1 — Wed, 05 Oct 2022 09:16:55 GMT

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))

Re: How to get the latest status from date per ID

RsQK — Wed, 05 Oct 2022 09:42:16 GMT

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:

Re: How to get the latest status from date per ID

Saif1 — Wed, 05 Oct 2022 11:02:18 GMT

thank you so much !!

Re: How to get the latest status from date per ID

agigliotti — Wed, 05 Oct 2022 12:49:45 GMT

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