Create a SubArray from another array? 2

[Carattini]

I need to create an array selecting a spesific range of data from another array. Is this pssible?

 

[Olaf Doschke]

Now here's what I ended up with:

USE "F:\Jobs\Recurrentes\Interno\Client Since\Tablas\Import.dbf" IN 0 Alias clientaccounts 

Exclusive

*!*	edwkey    = Identify the client
*!*	ssn       = Social security number Just for info
*!*	Open_dt1  = Open date of the account
*!*	close_dt1 = Close Date of the account
*!*	status    = O = Open C = Close Actual Status of the account

t1 = Seconds()
? "indexing data"
Index on edwkey    tag xedwkey
Index on open_dt1  tag xopen_dt1  additive
Index on close_dt1 tag xclose_dt1 additive
Index on status    tag xstatus    additive
? "data indexed in ",Seconds()-t1," seconds"
t1 = Seconds()

? "determine open dates of active clients"
Select edwkey, Min(open_dt1) as minopen, Max(close_dt1) As maxclose;
 From clientaccounts;
 Group By edwkey;
 Where status = 'O';
 Into Cursor curActiveclients Readwrite 

Local lnLoopcount
 
lnLoopcount = 0
Do While .T. 
  lnLoopcount = lnLoopcount + 1 
  Update curActiveclients set minopen = open_dt1 from clientaccounts;
    where curActiveclients.edwkey = clientaccounts.edwkey;
    and open_dt1 < minopen;
    and close_dt1 between curActiveclients.minopen And curActiveclients.maxclose
    
  If _tally=0
     Exit
  EndIf 
EndDo 

? "open dates determined in ",Seconds()-t1," seconds"
? lnLoopcount," loops needed"

The result is in curActiveClient, you get a record per client (edwkey), the minopen value is what I think you want, if my understanding is correct.

I have created some test data, as I don't have the Import.dbf, I created 2 million records in a cursor created by Create Cursor clientaccounts (edwkey C(15), acct_num I, open_dt1 D, close_dt1 D, status C(1))

It took about 20 seconds to generate the data (if you're interested I could also post the code to generate that test data), 40 seconds to index them and 10 seconds to get the needed date values.

I don't know the composition of your actual data, I think for real data it could be a little slower, the test data I generate will have more real gaps than you'll probably have, which will lead to only a few loops, but I'm quite sure this will reduce your computations to a few minutes.

What I don't do is compute the records for those clients, who only have closed accounts. It would be one or two additional SQLs to create a cursor curInactiveclients and then make a union of active and inactive clients for the update-sql loop.

Bye, Olaf.

 

[Olaf Doschke]

some error in copy& paste or whatever. The "Exclusive" in line 3 of my code should be at the end of line 1.

Oh, and I forgot that I did make some assumptions, as I didn't even asked: I assume Import.dbf is a pure dbf without any Import.cdx aside to it, I also assume you can have exclusive access to that dbf, therfore I opened it exclusive, which is needed to create indexes.

If you don't get exclusive access, an initial step would be to Select * from Import.dbf into cursor clientaccounts readwrite.

Bye, Olaf.

 

[Olaf Doschke]

How's it going, Carattini?

 

[Carattini]

Thanks This indexing solve the issue.