Loading max date with related data items

[emilia_siatrak]

Hello,

I am relatively new to QlikView scripting and am trying to use data load to restrict data items displayed in line with certain criteria. 

I have multiple data columns with dates (from and to) as well as respective amount value, which indicate whether a task is valid or not.

Only rows where respective amount = 1 are valid and should be loaded. To add complexity I have multiple rows as each valid row can have more than one set of dates. As end result only one row per Person ID is expected.

I am trying to load data row if those conditions are met but am struggling with making Qlik respect the data structure from the entry point. 

Summarising, there are few conditions I need to meet:

1) only load data items when Amount =1, i.e. if F1 Amount =1 or F2 Amount =1 - for each Person ID

2) once 1st condition is met, return data items related to row where from date is the most recent one.  So I need to select max(F1 from date) and the respective F1 to date, then max(F2 to from) and respective F2 to date.

As an example for the Person ID 1, I would like to get F1 from date = 01/04/2019, F1 to date = 23/03/2020, F2 from date = 11/12/2019, F2 to date = 14/03/2020.

I have tried few things but can't get it to work fully. I was able to restrict the load t values where the amount =1 but wasn't able to bring the correct To date for the max(FX from date).

I would be grateful for your suggestions.

Please see the example data attached.

Thank you, Emilia