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