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?

 

[bborissov]

Check ACOPY function in HELP.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Carattini]

Acopy makes an exact duplicate of the first array. but i need to make a selection from the first array before coping it to the second array.

Is their any whay to add a condition to select spesific data?

 

[ezopaci]

Any specific filter ? No. You will need use table engine. Create a cursor, append data into it from array and then COPY TO array with phrase FOR ...
Cursors are very fast, it mostly sits in RAM.

Tomas

 

[jimstarr]

Something like:

CREATE CURSOR mycursor FROM ARRAY myarray1
SELECT * FROM mycursor INTO ARRAY myarray2 WHERE [condition...]

Jim

 

[Carattini]

Thanks I'll Try that and let you know how it work out.

 

[Carattini]

I'm receiving an error message "Array dimensions are invalid"

This is how I build my fist Array:

PUBLIC ARRAY A1(1,1)
PUBLIC ARRAY A3(1,1)

USE "F:\Jobs\Recurrentes\Interno\Client Since\Tablas\Import.dbf" ALIAS Temp
SELECT * FROM Temp GROUP BY acct_num order BY edwkey,open_dt1 ASC;
INTO ARRAY A1

This is how I did the second array:
CREATE CURSOR mycursor FROM ARRAY A1
SELECT * FROM mycursor INTO ARRAY A3 ;
WHERE edwkey = Vedwkey ORDER BY open_dt1 ASC

The error comes up doing the first instruction to create the cursor. Any Ideas?

Thanks everybody for your help.

 

[Dan Freeman]

Create Cursor doesn't work that way. The array must contain the *schema* for the cursor, not the contents.

You'll have to use INSERT (or APPEND FROM) to move your data into your cursor, once you've legitimately created it.

 

[Olaf Doschke]

sorry, take a look at the acopy() parameters. You can copy a partial array.

Bye, Olaf.

 

[falconsight]

Why don't simply use

SELECT * FROM Temp GROUP BY acct_num order BY edwkey,open_dt1 ASC [b]INTO cursor[/b] myCursorName readwrite

?
That way you got the data in a fully editable form.
Then you can use a SQL-select command directly in the cursor, that way filtering further your data as you like.
Remember... You can manipulate a cursor exactly as it were an open table, but it resides entirely in memory. You can delete, browse, scan, etc. a cursor.
Really, I don't know what advantage an array may have over a cursor.

 

[Carattini]

Thanks Very much I'll try using the cursors with the readwrite and let you know how it works out.

 

[DSummZZZ]

Cursors are usually easier to work with, but you can also manipulate arrays using functions like:
FOR ... NEXT to loop through the items,
ASCAN() to search for an item,
DIMENSION to redimension or resize,
AINS() to insert an item somewhere in the middle,
ADEL() to remove an item, ...

The help file will show you the related functions.

So you could effectively loop through a current array looking at each item with a FOR loop, and if you find it use DIMENSION to add a new 'row' to the array to add the value to the bottom, or DIMENSION with AINS() to insert it in a particular place.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[DSummZZZ]

Mike,
Please elaborate. I use them quite often and they behave as they're documented to behave.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Carattini]

Thanks Guys I redid the code using cursors but didn't go any faster the code takes like tree days to run and I need it to work under a day.

In general I need to evaluate close to 370K Clients/Records that I have in one table

Another table has all 370K Clients with all the account under thous 370K clients this table has close to 1,650K records

I create a Cursor for each table

inside my loop I evaluate each record from 370K to a table/Cursor I create on the fly Using the 1,650K table this table containg the account for only that client that I am Evaluating from the 370K table

so I have tow loops one gouse to each record on the 370K
and then for each record their is another loop that build the Accounts for that clients and do the evaluation for each record then go to the previous loop for the next client

The thing that is making this so slow is the Select that I have to make to get all the accounts for each client from the 1,65K Cursor

if you like I can send you my code so you can take a look at it. I'm very tank full to all of you for the help as you probably gusted I don't have a lot of knowledge in VFP only what I have Gather from Help and You guys.

 

[Dan Freeman]

I suspect you're putting way too much effort into this. With that small quantity of records I'm hard pressed to think of a way I could POSSIBLY write a process that took more than a day, much less three.

You have a parent table with a related child table. The easiest way to construct a cursor containing all the child records for a single parent is a parameterized local view. Your entire process would be something like this:

Use YourLocalView Nodata
Use Clients in 0
Select Clients
Scan All
lnViewParameter = Clients.PrimaryKey
Requery("YourLocalView")
* Do something with the child records
Endscan

Why are you messing around with arrays at all?

 

[Olaf Doschke]

It even looks, like you could do it in one SQL with Group By. And it seems you don't even have indexes on those tables.

Bye, Olaf.

 

[Carattini]

The reason I'm messing with Arrays and Cursors is because it takes tree days for the code to run and I was looking for a way to better the performance of the code.

The purpose of the code is to find out since when the client is in business with the company.

The catch is that during the life of the client doing business with the company they may be time when the client close all of the accounts and then came back later on, so the client since date that they want should be the open date of the first account since it last came onboard with the company

in order to do this I need to compare all the accounts dates with each other so I can find out all the Gaps an determined What will be the new since date.

This is the original code using the tables before this post was posted if anyone knows how I can do this in a more efficient way I'll be more than grateful.

*------
?"Creando Cursor A2 Clientes con todas sus cuentas"
*This crates a cursor name A2 with all clients an all their accounts
*This will be use later to build another Cursor that contains all the account for a spesific client
USE "F:\Jobs\Recurrentes\Interno\Client Since\Tablas\Import.dbf" ALIAS Temp
SELECT * FROM Temp GROUP BY acct_num order BY edwkey,open_dt1 ASC;
INTO CURSOR A2 READWRITE

?"Creando Cursor Tempo para crear el cursor A1"
* This is a Temp Cursor just to order the previus Desending
* So When grouping I keep the Oldest account on record
SELECT * FROM A2 ORDER BY edwkey,Open_dt1 DESC ;
INTO CURSOR Tempo READWRITE

?"Creando cursor A1 cientes sin duplicados"
* This create the cursor A1 With Only the clients with the info
* of the oldest account on file.
SELECT edwkey,ssn,Open_dt1,close_dt1,status FROM Tempo GROUP BY 1;
INTO CURSOR A1 READWRITE

?"Contando A1"
A2QTT = RECCOUNT("A1")
? A2QTT

* This are the two loops that evaluate all dates
?"Buscando los Gaps"
*SET STEP ON
Recnum = 1
FOR Recnum = 1 TO A2QTT

CLEAR
? Recnum
GOTO RECORD Recnum IN A1
Vedwkey = A1.edwkey
Vopen = A1.Open_dt1
VClose = A1.close_dt1

SELECT * FROM A2 WHERE edwkey = Vedwkey ORDER BY open_dt1 ASC ;
INTO CURSOR A3 READWRITE

A1qtt = RECCOUNT("A3")
?A1qtt

FOR cuenta = 1 TO A1QTT

GOTO RECORD cuenta IN A3
TStatus = A3.status
TOpen = A3.open_dt1
TClose = A3.close_dt1

?TStatus
IF Tstatus = "C" THEN
IF BETWEEN(TOpen,Vopen,Vclose) THEN
IF TClose > Vclose THEN
Vclose = TClose
ENDIF
ELSE
IF TOpen > Vclose THEN
Vopen = TOpen
Vclose = TClose
ENDIF
ENDIF
ELSE
IF Tstatus = "O" THEN
IF BETWEEN(TOpen,Vopen,Vclose) THEN
IF TClose > Vclose THEN
Vclose = TClose
ENDIF
ELSE
IF TOpen > Vclose THEN
Vopen = TOpen
Vclose = TClose
ENDIF
ENDIF
cuenta = A1QTT + 1
ENDIF
ENDIF

LOOP
ENDFOR

replace A1.Open_dt1 with Vopen
replace A1.close_dt1 with VClose

LOOP
ENDFOR
*---------

 

[Olaf Doschke]

It's quite bad documented code there. I see my fear is fulfilled, you're not having any index on the cursors you create.

Also you do some GRoup By which wouldn't work since VFP8.

1. SELECT edwkey,ssn,Open_dt1,close_dt1,status FROM Tempo GROUP BY 1;
INTO CURSOR A1 READWRITE

What ssn,open_dt1,close_dt1, status values would you expect or need, if there are several records with the same edwkey? min, max, first, last, any?




2. SELECT * FROM A2 WHERE edwkey = Vedwkey ORDER BY open_dt1 ASC INTO CURSOR A3 READWRITE

same question, what values of other fields would you expect and need from all other fields?


3. misleading var names:

A2QTT = RECCOUNT("A1")
A1qtt = RECCOUNT("A3")

???

4. No need for a loop directly before Endfor

5. use EXIT instead of cuenta = A1QTT + 1 to end the loop

6. don't copy code, adjust your IF statement simply

If Tstatus = "C" Or Tstatus = "O" Then
   If Between(TOpen,Vopen,Vclose) Then
      If TClose > Vclose Then
         Vclose = TClose
      Endif
   Else
      If TOpen > Vclose Then
         Vopen = TOpen
         Vclose = TClose
      Endif
   Endif

   If Tstatus = "O" Then
      Exit
   Endif
Endif

No wonder you can't improve that, it's a total mess.

Let's see what you end up with in Vopen and Vclose:
You change Vopen to Topen every time Topen>Vclose, that's interesting, I donÄt understand why. You change Vclose to TClose, everytime Tclose>Vclose, that's simply calculating Max(Tclose), but you also do so, if Topen>Vclose.

The catch is that during the life of the client doing business with the company they may be time when the client close all of the accounts and then came back later on, so the client since date that they want should be the open date of the first account since it last came onboard with the company

Rereading this you don't want simply Min(Open_dt1) and Max(Close_dt1). The time intervals regarding seperate accounts can overlap and there can be intervals without any account. Let's talk in examples here:

A client "client A" has account 1 from 01/01/1990 to 01/01/1995, account 2 from 01/01/1993 to 01/01/1998 and account 3 from 01/01/1999 to 12/31/9999 (no end yet).

What do you need in the final result
wither both client realtionships
client A, from 01/01/1990 to 01/01/1998
client A, from 01/01/1999 to 21/31/9999

or only the last clientship
client A, from 01/01/1999 to 21/31/9999

And do you set close_dt1 to 12/31/9999 like I did or do you leave it EMPTY() or even NULL if the account is not yet cloesd?

Bye, Olaf.

 

[Carattini]

Hi Olaf Thanks for taking the time to help this lost soul.

Sory for not documenting further is not final.

Can you tell me how to index the cursors?

Yes I do group by from vertion 8 I use a command
"SET ENGINEBEHAVIOR 70"

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

The reason for this table is to have all the clients to be evaluated with the info of the oldest account so it can be evaluated with the rest of the accounts on file, the fields neaded for this are edwkey,Open_dt1,close_dt1

2. Same Values as the previus table but filtering with the edwkey now I have all the accounts in a chronological order for the client I'm currently evaluating in this case The Status is needed because when evaluating the first Opend Acount don't need to evaluate any more because that account is currently open.

3. This are tow variables that contains the total QTT of records of each table so I know when to stop. I'll Change the numbres this had happend because all the changes I've bean doing.

4. Thanks

5. Thanks

6. Thanks

In the example you wrote the answer is from 01/01/1999 to 21/31/9999

But Their are some other instances where it is not that simple to identify the gaps like:

account 1 from 01/01/1985 to 01/01/1995,
account 2 from 01/01/1990 to 01/01/1993,
account 3 from 01/01/1994 to 01/01/1998,
account 4 from 01/01/1997 to 12/31/9999,

as you can see their appears to be a gap between 2 and 3 but because 1 is close later it realy is not a real gap thats whay I keep The Open and Close of the oldest in memory While Comparing to the others and the values are change during the proces so I get the real Gap if any. The value I whant is the new open Date with no gaps the close Date is use only for evaluation puropose but do not need to export.

The tables That I'm evaluating has 12/31/9999 for no end date but the table I create has null if no end date. this is not really important.

I'll do changes you sugest to see how they work but don't know how to index the cursors if you can shed some light I'll apreciated.

 

[Olaf Doschke]

Thanks for answering my questions, I think I now could write a single SQL or perhaps two, that will do this stuff.

I fear you won't gain much performance fomr my tips, but more readability and better understandable code.

In your last example, I'm not sure what you now would compute as Vopen. As there is no real gap, I assume you want 01/01/1985, right? Even though the account 1 opened then is already closed, you would want that date, as it's the first entry date for the client, who's a client from then onwards.

One more hint with the loops: simply do

Select a1
scan
...
endscan
[code]

This will loop through all records of a1, no need to GOTO RECORD with each record number. You exit scan..endscan loops with Exit too.

Now let me see if I can come up with some sql replacing all this code.

Bye, Olaf.