Is this a case for using RANK, PEEK, or PREVIOUS in a load script?

[pam1990]

I  plan to create a load script using SQL for one of our tables.

From there I plan to use resident script for what I need to accomplish, but I do not know how to accomplish it in Qlik Sense.

I am thinking the Rank function is needed, but it's not as simple as that. Likewise selecting the record corresponding to the maximum operating statement date is not correct. I will explain more on that in a moment.

Here is the criteria for the correct record:

Select the operating statement row by getting the

most recent actual, unless

if brief statement date is > actual statement date, then brief  else

if no brief or actual, then proforma

NOTE:

actual, brief, and proforma are user defined types of a longer list of possible user defined types. We only report off of one of these three.

Also, a property can have more than one record.

If the maximum statement date is used to get the record, it is possible for the date of the proforma to be greater than the actual or brief record. We do not want the proforma record in those instances.

So I am wondering how to get the records we need as we need them?

Below is an image of the data. The key group is the first column, which is the PROP_CODE. I do not have an example of a property with PROFORMA only, but we do have them on occasion.