Loading max date with related data items

emilia_siatrak — Fri, 15 Jan 2021 21:13:10 GMT

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

topic Loading max date with related data items in QlikView

Loading max date with related data items