Help: Improving "for each" solution...

Thu, 28 Sep 2017 04:30:44 GMT

//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.-

Re: Help: Improving "for each" solution...

Clever_Anjos — Wed, 04 Oct 2017 11:56:09 GMT

Instead of a for approach please try this IntervalMatch

topic Help: Improving "for each" solution... in QlikView

Help: Improving "for each" solution...

Re: Help: Improving "for each" solution...