OBI field Most Advanced Requisition identifier retrieves no rows if there is single job submission
Summary:
We would like to retrieve the most latest job submission submitted by a candidate, For this purpose we are using the
available OBI field "Candidate Most Advanced Job Application"."Most Advanced Requisition Identifier"
However, "Most Advanced Requisition Identifier" field only retrieves data if and only if the candidates has done more than 1 submissions, for candidates where there is a single submission, this field retrieves no data.
To circumvent this, we have tried to write a case statement in the column formula as below:
CASE WHEN (IFNULL("Candidate Most Advanced Job Application"."Most Advanced Requisition Identifier", 1) = '1') THEN "Requisition Identification"."Req. Identifier" ELSE "Candidate Most Advanced Job Application"."Most Advanced Requisition Identifier" END