Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dhasharadh
Creator III
Creator III

how to check 2 rows statuses and decide the final status

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'.

dhasharadh_0-1699455083816.png

 

many thanks in advance.

 

Labels (1)
1 Reply
vincent_ardiet_
Specialist
Specialist

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;