how to check 2 rows statuses and decide the final status

dhasharadh — Wed, 08 Nov 2023 15:11:34 GMT

Hi All,

we have multiple statuses for a single code with a very minor time difference.

now i am trying to implement below logic.

1. if the code status is reached to 'COMPLETED' and before this status if the code status is 'IDENTIFICATION_COMPLETED'

then status should be 'Automatic Approved'.

2. if the code status is reached to 'PENDING_MANUAL_REVIEW' and before this, if the code status is 'IDENTIFICATION_COMPLETED' then status should be like 'Manual check required'.

3. and we want only the one record to each code, that record must be with the latest status of the code. considering the max time.(I need to join this script with an other main table with code as key field)

in other way, if the code statuses are changed from 'IDENTIFICATION_COMPLETED' to 'COMPLETED' --> then status as 'Auto Approved'.

if the code statuses are changed from 'IDENTIFICATION_COMPLETED' to 'PENDING_MANUAL_REVIEW' --> then status as 'Manual check required'.

many thanks in advance.

Re: how to check 2 rows statuses and decide the final status

vincent_ardiet_ — Fri, 10 Nov 2023 09:51:09 GMT

You can create a new field "FINAL_STATUS", and then you can filter on it if you want to keep only those rows:

Load *, If(Previous(CODE)=CODE And Previous(STATUS)='IDENTIFICATION_COMPLETED',Pick(Match(STATUS,'COMPLETED','PENDING_MANUAL_REVIEW'),'Automatic Approved','Manual check required'),Null()) as FINAL_STATUS Resident MySourceTable Order By CODE,CREATION_TIME;

topic Re: how to check 2 rows statuses and decide the final status in App Development

how to check 2 rows statuses and decide the final status

Re: how to check 2 rows statuses and decide the final status