how to check 2 rows statuses and decide the final status

[dhasharadh]

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.

[vincent_ardiet_]

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;