//Hi guys, i have the following situation:
Payments:
Load * inline //PaymentDate Format is DD/MM/YYYY
[
Account, PaymentDate
1, 02/01/2017
1, 05/01/2017
1, 07/01/2017
1, 03/02/2017
1, 07/02/2017
2, 07/02/2017
2, 10/02/2017
2, 22/02/2017
2, 27/02/2017
3, 17/06/2017
];
Assignments:
Load * inline
[
Account, Agency, DateFrom, DateTo
1, 50, 01/01/2017, 31/01/2017
1, 60, 01/02/2017, 15/02/2017
2, 10, 05/02/2017, 20/02/2017
2, 15, 21/02/2017, 20/12/2017
3, 25, 01/04/2017, 01/12/2017
];
//And i need to get the first payment date for each account, considering the assignment ranges. So the hoped result should be like this:
//--
//Account, FirstPaymentDatePerRange, Agency
//1, 02/01/2017, 50
//1, 03/02/2017, 60
//2, 07/02/2017, 10
//2, 22/02/2017, 15
//3, 17/06/2017, 25
//
//I find a solution by looping Assignments source, generating a new key. The result table:
//Account PaymentDate DateFrom DateTo Agency NewKey
//1 02/01/2017 01/01/2017 31/01/2017 50 1-01/01/2017-31/01/2017
//1 05/01/2017 01/01/2017 31/01/2017 50 1-01/01/2017-31/01/2017
//1 07/01/2017 01/01/2017 31/01/2017 50 1-01/01/2017-31/01/2017
//1 03/02/2017 01/02/2017 15/02/2017 60 1-01/02/2017-15/02/2017
//1 07/02/2017 01/02/2017 15/02/2017 60 1-01/02/2017-15/02/2017
//2 07/02/2017 05/02/2017 20/02/2017 10 2-05/02/2017-20/02/2017
//2 10/02/2017 05/02/2017 20/02/2017 10 2-05/02/2017-20/02/2017
//2 22/02/2017 21/02/2017 20/12/2017 15 2-21/02/2017-20/12/2017
//2 27/02/2017 21/02/2017 20/12/2017 15 2-21/02/2017-20/12/2017
//3 17/06/2017 01/04/2017 01/12/2017 25 3-01/04/2017-01/12/2017
// Finally, with an extra step, getting the minimum PaymentDate by grouping account and agency (and joining on Payments table), i can have the table i was looking for:
//1 02/01/2017 01/01/2017 31/01/2017 50 1-01/01/2017-31/01/2017
//1 03/02/2017 01/02/2017 15/02/2017 60 1-01/02/2017-15/02/2017
//2 07/02/2017 05/02/2017 20/02/2017 10 2-05/02/2017-20/02/2017
//2 22/02/2017 21/02/2017 20/12/2017 15 2-21/02/2017-20/12/2017
//3 17/06/2017 01/04/2017 01/12/2017 25 3-01/04/2017-01/12/2017
//Now, this solution works fine with few rows, But it's too slow when my Payments table is up 250.000 and the assignments about 2.500.000 rows.
NewPayments:
LOAD * INLINE [
Account, PaymentDate, NewKey, DateFrom, DateTo
];
FOR vRow = 0 to NoOfRows('Assignments') -1
LET vAccount = Peek('Account',$(vRow),'Assignments');
LET vDateFrom = Peek('DateFrom',$(vRow),'Assignments');
LET vDateTo = Peek('DateTo',$(vRow),'Assignments');
LET vAgency = Peek('Agency',$(vRow),'Assignments');
Concatenate(NewPayments)
LOAD
Account,
PaymentDate,
$(vAccount) &'-'& '$(vDateFrom)' & '-' & '$(vDateTo)' AS NewKey,
'$(vDateFrom)' as DateFrom,
'$(vDateTo)' as DateTo,
'$(vAgency)' as Agency
Resident
Payments
where
Account = $(vAccount)
and PaymentDate >= Date('$(vDateFrom)')
and PaymentDate < Date('$(vDateTo)');
NEXT;
DROP TABLE Assignments;
DROP TABLE Payments;
//Please, any sugestion to improve the performance?
//Thanks in advance!
//
//R.-
//
